Symptoms
You notice that in your SQL databases directory the .LDF files are growing permanently.
Solution
Set the recovery mode of your SQL Server databases to ‘simple’.
Step-by-step instructions
- Perform a full-backup of your SQL Server databases.
Note: This is very important since switching from the full or bulk-logged recovery model to the simple recovery model breaks the backup log chain. Therefore, it is strongly recommend to back up the log immediately before switching, which allows you to recover the database up to that point. After switching, you need to take periodic data backups to protect your data and to truncate the inactive portion of the transaction log. [Source] - Switch recovery mode of SQL databases to SIMPLE.
(See also: What is simple recovery mode?)Important Note:
- Shrink the transaction log (.LDF) files.
- Perform a full-backup of your SQL Server databases.
“The Simple recovery model lets you restore the database to the point from which it was last backed up. However, this recovery model does not enable you to restore the database to the point of failure or to a particular time.” [Source]
Optionally you can use a script for the steps described above:
Download: SQLScript_SetRecoveryModeSimple.zip
Additional information
Please note that you should only do this if you should have been running in SIMPLE recovery mode in the first place. You should switch recovery modes because you want to change your ability to recover the data, not because your log files got big.
Check this out: http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-proper-transaction-log-size-management.aspx
Thanks.
-D.
I’ve had the problem with the ldf file that grew over 250 GB. I’ve read most of the blogs and microsoft technical support. All useless information until I came across this one. It’s clear and concise. It’s got a step by step procedure how to reduce the size of the ldf file safely. Do not simply delete the file as you will destroy for database. This is the only safe option. Thank you for your help. If you had a donation option I would have made payment. This is how valuable this information was for me. Thank you. D.
@David: I’m glad it helped you out.
Thanks for your hints and your instructions. This guidline gave me the assurance to be on the correct way 😉
regards
viktor
great stuff thanks for sharing ! The Transaction log shrinking is beginning to clear up. As i found another helpful post for the same see here: http://www.sqlserverlogexplorer.com/shrink-transaction-file/
fantastic post! This is so chock full of users information and the resources you provided was helpful to me. You may have a look on this URL:http://www.sqlmvp.org/transaction-log-is-too-big-or-growing-unexpectedly/
This blog was also very helpful described how to prevent growth and much more.