Shrinking the LDF file

[Tyler W. Stowe]

My database is 80mb in size (mdf) and my transaction log is 6.7gb (ldf). Is there something that I should be doing to reduce the size of the LDF file?

[OlivierV]

Hi Tyler.

I had the same issue. I do the following to reduce the size :
Go to start/program/Microsoft SQL server/Query analyzer. Enter the sa password then OK.
In the toolbar, select the insight database, it should display the master database by default.

Now find the name of SIM database in program files/microsoft sql server/mssql$sim(or your database instance)/data. Here you will find an insight_xxx.mdf file and an insight_xxx_log.ldf file. The name to remember is insight_xxx (mine is insight_v1_0_14224218). Go back to the query analyzer window.

In the window, type :
backup log insight_xxx with no_log
for example :
backup log insight_v0_1_14224218 with no_log
then press F5 to run.

Delete the line then type :
dbcc shrinkfile(insight_xxx_log,sizeinMB)
for example :
dbcc shrinkfile(insight_v1_0_14224218_log,40)
then press F5 to run

This should reduce the log file to 40 MB.

You can also set the log file not to grow above xxMB in the SQL enterprise manager but this could lead you to some issues when the log is full. Check in this forum about cpu usage, I have posted a message about this.

These commands can also be used in a dos session with isql, I try now to automatize the file size check then launch the shrinkfile sql commands if the size is too large.

In my case, also, after the shrinkfile, the log files reduces itself after a few hours. Is it today at 1,5 MB after beeing reduced at 40 MB yesterday.
Hope this helps.
Regards.

[Claas Kruhoefer]

Hello,

if you are running MSSQL2000 you can also modify the DB Maintenance plan ( Transactionlogbackup ) and add a second step using this sql commands
-------------------------------------
use Insight_v1_0
DBCC SHRINKfile(Insight_v1_0_log, 0)
-------------------------------------

just adjust the database name to your db name and the logfilename also.

I run the transactionlog backup every 30 minutes, my database ist ~450MB.

It produces round about 600-800 MB transactionlogs per hour.

I also set up to kill every transactionlogbackup that is older than 2 hrs. this keeps the amount of data low.

[Tyler W. Stowe]

Thank you to both of you. The shrinking worked, and I will get it scheduled. I was surprised this worked when I thought I had tried the same idea throught the GUI Enterprise Manager and it accomplished nothing. Oh well. It is a solution!