What to do when ldf file grows

If you noticed that the free space on your hard drive is shrinking and you found that the log LDF file is taking most of it (and growing) don’t panic.
it is possible that the reason is that your recovery mode is is set to full.
to change this:

1) Right click on the db (in SQL server Managment Studio) and click properties.

2) Navigate to the option tab and make Make sure that the recovery model is set to simple.

If a full recovery mode is needed:

Issue regular backups (in the transaction log shipping tab)
and if Auto Shrink is enabled, the file will eventually shrink (though not immediatly)

If you need to shrink the file immediately:

•    Open MS SQL Server Management Studio, connect to Database Engine

•    Select New Query and type:  backup log <db_name> with truncate_only

(this is discontinued in SQL SERVER 2008 – Microsoft recommends using Simple mode instead)
•    Execute this query (press F5)
•    Right click on Database name and navigate to Tasks->Shrink ->Files:

Share on RedditEmail this to someoneShare on Google+Share on LinkedInShare on FacebookDigg thisTweet about this on Twitter

3 thoughts on “What to do when ldf file grows

  1. Pingback: DB Tricks

  2. Pingback: DB Tricks » “Shrink failed for Database” when attempting to shrink a data file.

Leave a Reply

Your email address will not be published. Required fields are marked *