Large Transaction Log files in SQL Server 2008

Published on
10,912 Points
1 Endorsement
Last Modified:
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries much smaller transaction log files.

Here is a recommended MS document showing proper procedures for truncating log size. I recommend you review this article before proceeding: http://msdn.microsoft.com/en-us/library/ms189085(v=SQL.100).aspx

The recovery model in SQL server is set by default for all databases to "FULL". In the full recovery model all transactions from the beginning of the database creation are kept in a file that grows along with the database file until it reaches outrages sizes. There are methods of shrinking these transaction log files but that doesn't always provide a long term solution. Full recovery is recommended so that in the event of corruption or failure the database can be recovered to the very minute the database went down. Simple recovery will only allow you to recover the database to the last backup copy of the database.

If you are okay with losing a small amount of transaction log you can switch to the simple recovery model and free up a large amount of space on your server, by following these simple steps as it worked for me:

Perform a Full Backup of the Database

Open SQL Management Studio

Browse to the database with the large transaction log file and right click the database, go to "Properties" Click on "Options" and change the recovery model to "Simple" then click "OK". You have now changed the logging from "FULL" to "SIMPLE".

In order for the log size to decrease you have to perform a checkpoint event, such as another full backup (another recommended step would be to run dbcc shrinkfile at this time). Transactions can hang and SIMPLE logging my not take place so if the database log size does not shrink as a last resort you can take the database offline and then bring it back online to enact the change as explained below:

Browse to the Database, right click, "Tasks" => "Take Offline". Then repeat the step but choose "Bring Online". You will now notice the transaction log file is significantly smaller.

Done, you have accomplished the goal of reducing the transaction log size.

In the event that you simply wanted to reduce the size of the file, but still want full recovery you can re-enable "FULL" recovery at this time.  Also worth checking the initial size allocation for your Transaction Log as explained in the Microsoft Article above.

That's all for today folks, let me know if this helps.
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free