A frequent question that students bring to class when I am teaching is this: "My transaction log is growing out of all proportion - what can I do?" I always answer with another question "Are you backing it up?" The student usually replies "Of course, we backup the entire database every night". "But are you backing up the LOG?" At this point the student lets me know that if we solve this dilemma, the boss will feel the cost of the class will be worth it right there and then. Well, the answer, believe it or not, is to back up the log twice. Yes, twice. Let me explain...

As I discussed in an earlier blog, backing up the transaction log will not only backup the latest committed transactions within the log but will also truncate the log file. Truncation means that the transactions that were backed up are removed from the log file, freeing up space within the log file for new transactions. The truth is, if you don't backup the transaction log, it will continue to grow forever, until you run out of disk space. New DBA's to SQL Server assume that the Full Database backup truncates the transaction log - it doesn't. It does take a snapshot of the transaction log at the very end, so that transactions committed during a long running Full backup are actually backed up too (which is quite clever) but it does not truncate the log. So students come to class with a 100MB Database that has a 16GB Transaction Log. Yikes. Well, the long term solution is to backup the transaction log frequently. This will keep the transaction log "lean and mean". But how frequent is frequent? Well, it depends. Generally, we try to keep the transaction log under 50% of the size of the data files. If it grows beyond this, then we back it up more frequently. This is why for some very active databases, we may backup the log every 15 minutes. As I said, it depends.

OK, so let's get back to the 16GB log file. What should we do to get this back to a reasonable size? Well, backup the log first of all. It contains some valuable updates. That will truncate the log but will not make the file any smaller. So secondly, perform a DBCC SHRINKFILE operation. Now, when we do this we may not see any shrinkage (Did you see that Seinfeld episode? I digress...). So let's refer to Books Online which states in its wisdom: "it may take two backups to actually free the space". Yes, if at first you don't succeed, try again. The actual solution is to run the BACKUP LOG/DBCC SHRINKFILE sequence twice!

The second time around, you should see significant shrinkage. (Sorry, George!)

Later,
Brian

Managing the Size of the Transaction Log File (on MSDN):
http://msdn.microsoft.com/en-us/library/ms365418.aspx