How to reduce the log (LDF) file size?

[davidchardonnet]

Hello,

Is there a simple procedure to reduce the LDF file? On the database I work on, it's about 2 GigaBytes and I can't insert anything in it.
I work on SQL Server 7.0
I tried The DBCC SHRINKDATABASE command, it seems to work, but the file size is the same.

Even the FAQ is not clear enough...

Thank you

David

 

[nigelrivett]

set that database recovery model to simple (unless you are taking log backups).
Then try the shrink.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[theoxyde]

You can also use the command:

backup log [myData] with truncate_only

myData is the name of your database (include braces). This will truncate the transaction log. Make sure you perform a backup immediately following.

 

[davidchardonnet]

nigerivett,

I can't find where you set the data recovery model to simple. How do you do that? Is there a command line?

Thank you.

 

[davidchardonnet]

I forgot to say it's on a SQL Server 7.0 Database...

 

[nigelrivett]

In enterprise manager right click on the database and look at the properties.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[paul172]

Guys,

When using the DBCC SHRINKFILE command how do you know what the target size should be when stipulating it. Also what happens if the log file cannot shrink to that size, does it throw up an error, or get as close to it as possible.

 

[nigelrivett]

It will shrink as muhc as it can.
You just have to guess how much you should shrink it to.
The log will grow again if you shrink it too much. Growing a file is a very resource intensive thing so you shouldn't shrink just to temporarily save space - try to keep the sizes as static as possible nad only shrink after exceptional (or accidental ) processing.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.