How to empty log file in SQL 2005?

I have been trying to shrink or emptly a log file in one of my Databases but
to no evail. When I try to shrink it only shrinks it by a little bit and
grows to the original size within seconds.
Is there a way to completely empty a log file. It about 80 GB and something
is now right...
Thank you.
Peace in Christ
Marco Napoli
http://www.ourlovingmother.orgMarco Napoli wrote:
> I have been trying to shrink or emptly a log file in one of my Databases b
ut
> to no evail. When I try to shrink it only shrinks it by a little bit and
> grows to the original size within seconds.
> Is there a way to completely empty a log file. It about 80 GB and somethin
g
> is now right...
>
> Thank you.
>
When was the last time you backed up the transaction log? Sounds like
never, which means there are transactions piling up in there. Until
those are flushed out, you can't shrink it.|||can u change it to simple mode?
"Marco Napoli" <marco@.avantitecnospam.com> glsD:uZewtPilGHA.5108@.TK2MSFTNGP03.phx
.gbl...
>I have been trying to shrink or emptly a log file in one of my Databases
>but to no evail. When I try to shrink it only shrinks it by a little bit
>and grows to the original size within seconds.
> Is there a way to completely empty a log file. It about 80 GB and
> something is now right...
>
> Thank you.
> --
> Peace in Christ
> Marco Napoli
> http://www.ourlovingmother.org
>|||How would I flush them?
Thanks.
Marco
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OA34pVilGHA.2136@.TK2MSFTNGP04.phx.gbl...
> Marco Napoli wrote:
> When was the last time you backed up the transaction log? Sounds like
> never, which means there are transactions piling up in there. Until those
> are flushed out, you can't shrink it.|||Marco Napoli wrote:
> How would I flush them?
>
If you don't need the ability to do point-in-time recovery, put the
database in Simple recovery mode and the log will flush itself at the
next checkpoint. With an 80GB log file, this might take a few minutes.
I'm guessing you aren't concerned with point-in-time recovery, since
you're not backing up the transaction log. If I'm wrong, then you need
to do some reading about backups and the transaction log, because you're
not taking care of your database right now.
To solve your immediate problem, run BACKUP LOG WITH TRUNCATE_ONLY
against your database.|||As of right now the point-in-time recovery is not necessary but I am working
my way to that. The maintenance plan makes a full back at certain time.
Thank you for the help I will try it.
Marco
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uQRyYrilGHA.4772@.TK2MSFTNGP04.phx.gbl...
> Marco Napoli wrote:
> If you don't need the ability to do point-in-time recovery, put the
> database in Simple recovery mode and the log will flush itself at the next
> checkpoint. With an 80GB log file, this might take a few minutes.
> I'm guessing you aren't concerned with point-in-time recovery, since
> you're not backing up the transaction log. If I'm wrong, then you need to
> do some reading about backups and the transaction log, because you're not
> taking care of your database right now.
> To solve your immediate problem, run BACKUP LOG WITH TRUNCATE_ONLY against
> your database.|||Marco Napoli wrote:
> As of right now the point-in-time recovery is not necessary but I am worki
ng
> my way to that. The maintenance plan makes a full back at certain time.
>
Do yourself a favor and stop using maintenance plans. Learn what the
various options in a maintenance plan do, understand why they are used,
and write your own routines. Had you done that with backups, instead of
clicking buttons in a wizard, you would have understood the transaction
log and not be staring at an 80GB log file now. Wizards have their
place, but database administration is not one of them.|||Thank you so much that solved my problem.
Marco
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uQRyYrilGHA.4772@.TK2MSFTNGP04.phx.gbl...
> Marco Napoli wrote:
> If you don't need the ability to do point-in-time recovery, put the
> database in Simple recovery mode and the log will flush itself at the next
> checkpoint. With an 80GB log file, this might take a few minutes.
> I'm guessing you aren't concerned with point-in-time recovery, since
> you're not backing up the transaction log. If I'm wrong, then you need to
> do some reading about backups and the transaction log, because you're not
> taking care of your database right now.
> To solve your immediate problem, run BACKUP LOG WITH TRUNCATE_ONLY against
> your database.|||Thank you for the tip.
Marco
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:%23wbTOEjlGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Marco Napoli wrote:
> Do yourself a favor and stop using maintenance plans. Learn what the
> various options in a maintenance plan do, understand why they are used,
> and write your own routines. Had you done that with backups, instead of
> clicking buttons in a wizard, you would have understood the transaction
> log and not be staring at an 80GB log file now. Wizards have their place,
> but database administration is not one of them.
>

how to empty a stored procedure in ms sql server management studio express

hi everyone,

I have a db based on the Tracking_Schema.sql / Tracking_Logic.sql (find in &windir%/Microsoft.NET/Framework/v3.0/Windows Workflow Foundation/SQL/EN), so after executing both of them I get several stored procedures, especially dbo.GetWorkflows. And I have a solution in VS05 which when executed is filling this stored procedure with Instance-Id′s. My question is: how is the working command (like exec, truncate,..) to empty my st.procedure, not to drop/delete it?

Thanks in advance, best regards

bg

hi bg,

stored procedures can only be executed and not "filled" like "tables"... so they can not be emptied (is that english? )

so what are you trying to do? if you want to empty a specific table, you can open SQL Server Management Studio Express, select the table, access the "Open table" feature, select all rows and delete them (if not referenced in foreign key constraint) or do the same in a query window executing

TRUNCATE TABLE schema_name.table_name

or

DELETE FROM schema_name.table_name WHERE filter_criterion..

regards

sql

How to eliminate the NULL field values

I am importing an Access .mdb file into MS SQL server, and empty fields where the default value is "", change into NULL. This is a problem when I re-export a result set and have to apply a procedure to clean these values. Is there a way to eliminate this? . . . . and what have I missed?Check 'allow nulls' and 'default value' settings in the access tables.|||and to solve for the export problem...

Use a query to export the table, and use
Select ISNULL(yourfield,"") AS FIELD1...sql