Shrinking Transaction Logs

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.