Shrinking Transaction Logs

, 2011-08-31

I see questions on forums all the time about DBA’s or System Admins finding they are out of drive space due to a very large transaction log.  Post vary a bit on what the admin has tried but a common theme is that they have tried shrinking the file but it didn’t shrink and a plea for help.

Several immediate issues stand out with these post for help.  First is that regular transaction log backups are not occurring.  Second is that if regular backups are not happening, is the admin adequately ready to restore this system if they had to.  What is their recovery plan and the service level agreement for this system?

A quick fix for the users with the swollen transaction log that is crippling the server due to filling up all the available drive space is pretty simple.  Issue a command that will issue a check point in the transaction log and then shrink the file.  A permanent solution is to then start taking regular transaction log backups if you need to be able to recover to a point in time other than since the last full backup.  If you don’t have the need for that type of service level agreement then SIMPLE mode or BULK LOGGED may be your answer.

To issue a check point you have a couple of options.  With all versions of SQL Server you can simply backup the log.  The issue with that in most cases is that you are in a pinch and need to resolve this issue immediately.  You don’t have time to backup a huge log and even if you did have the time, you probably have no need for the huge transaction log.  You just need to quickly issue a check point.  What do you do?

With SQL 2000 and 2005 you can backup the log with TRUNCATE_ONLY.  The proper syntax is something like “BACKUP LOG [DB_NAME] WITH TRUNACTE_ONLY”.  With SQL 20008 and above the “TRUNCATE_ONLY” no longer works as it was deprecated.  A nice workaround is to backup the log to a null file system.  To do that simply use “BACKUP LOG [DB_NAME] TO DISK = ‘NUL’”.  It almost seems like cheating doesn’t it.

Another option is to change the recovery model from FULL to SIMPLE, shrink the log and then set the recovery mode back to FULL.  This option works just as well as TRUNCATE_ONLY or backing up to the NULL disk.

Regardless of which option that you chose to issue the check point and reclaim the wasted space, you should only shrink to a certain target size that you need.  If you shrink the log file to small then it will just have to grow again causing a negative impact to your system.  If you are not certain what size you need, just take a SWAG at it and monitor.  As for how often to take regular transaction log backups, well that depends on your SLA.  I have some systems that backup every 10 minutes, others once an hour.  IT DEPENDS on your environment and needs.

You should also take a full backup.  If something happens between now and your next full backup, you will only be able to recover to your last known good backup.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads