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:
Posted: September 17th, 2008 under SQL Server.
Tags: LDF, SQL Server
Comments
Pingback from DB Tricks » “Shrink failed for Database” when attempting to shrink a data file.
Time September 22, 2009 at 7:02 pm
[...] clear some space to allow the process to complete. If you can not free some disk space, Try to shrink the transaction log and [...]
Pingback from DB Tricks
Time July 28, 2009 at 10:54 am
[...] when running a big transaction, the transaction log can become full or simply grow to a huge size . while you can sometimes free some space using “backup log db-name with truncate_only” [...]