I have written about log file growth several times. Parts of this article are excerpted from those previous articles I have written. The reason why I return to this topic periodically is that I see the problem so often. Each new client database I see seems to have a scheduled job to shrink the log file every night. Or worse, they are shrinking the entire database each night. ( you should never shrink your database or data file. It completely fragments all your indexes, among other bad things it can do. See Paul Randal’s blog about this)
Uncontrolled log file growth is caused by completed transactions not being removed from the log when they should be. This usually results from an incomplete understanding of database recovery models. The recovery model of the database determines how and when completed transactions are removed from the log file to make room for new records. A SQL Server database has 3 recovery modes to which it can be set, SIMPLE, FULL, or BULK LOGGED. We can consider BULK LOGGED to be the same as FULL for the purposes of this article.
Full Recovery Mode
Most enterprise installations of SQL Server run in FULL recovery mode because it offers a much lower risk of data loss.. In FULL recovery mode, transaction records are not removed from the log file until the transaction log is backed up. Therefore, if your database is set to FULL mode, you must back up the log periodically in order to create free space in the log file. Failing to back up your log file while it is in FULL recovery mode is the most common cause of log file growth problems.
You may find that the log file remains huge even though you are backing up the logs regularly. Keep in mind that a log file can grow automatically but it will not shrink automatically. Some extraordinary event in the past may have caused the file to grow out of proportion to its normal needs and it has remained at that size although very little of the space in the file is currently being used. In this case it is permissible to shrink the log file to get it down to a reasonable size. However, It should not be necessary to shrink it on a regular basis. (And, as mentioned above, never, ever shrink the data file(s) or your database itself.
In normal operation, a log file in FULL recovery mode will grow to the size it needs to be to contain the maximum number of transaction records it accumulates between backups. The file size will be relatively large if there is a high transaction load on the database or if the interval between backups is long. It will be smaller if the transaction load is lower or if the backups are more frequent. In either case, the file should stabilize at the size it needs to be.
Simple Recovery Mode
SIMPLE Recovery mode is, well, simpler. Log records are deleted as soon as their transaction completes. This usually prevents log file growth because transactions normally are small and they complete quickly. However, if you are loading a million new records every night and that is being done in a single transaction, your log file will be pretty large because the log file expands to a size that will accommodate all the records from your biggest transactions.
You don’t need to back up your log file in SIMPLE mode. In fact, you will get an error if you try. The drawback to SIMPLE recovery mode is that your only option for recovering a database is to restore the last full backup you made. That means you will lose everything that happened since the last backup. This is why SIMPLE mode must be used only in cases where that degree of data loss is an acceptable risk.
When is Shrinking the Log File OK?
Shrinking the log file is useful when an extraordinary event expands the log much larger than normal. However, regular shrinking of the log below its normal size is a waste of time and causes the file to have go through many expensive growth cycles to get back to its natural size. This can cause a heavier load on the disks and delays in processing. Shrinking in this case does not give you any additional usable disk space because the log will quickly grow back to its necessary size to handle your biggest transaction.
Other Causes of Log File Growth
In some cases you can be backing up your transaction logs regularly and still find uncontrolled, continuous file growth. A number of conditions can cause this. Here are a couple I have run across:
Tables are being replicated, but the distribution database is unavailable. Records involving replicated transactions cannot be removed from the log until they are sent to the distribution database. There are several things that can cause this.
Network issues are preventing communication with the distribution database.
Someone attempted to disable replication manually but did not do a complete job.
Due to a communication breakdown, someone turned off or decommissioned the distribution server.
I have run into this a few times and it is not as bone-headed as it sounds. Many sites have configured replication that became unnecessary at some point. Everyone has forgotten about it. At some point, it is decided to remove an old and apparently unused server which in fact is where the distribution database is parked.
Since no one is accessing the subscriber database any more, no one notices that anything is wrong until the log file fills up the disk.
This article is far from a complete coverage of the log backups, recovery modes, etc, but I hope this has helped your understanding of the issues behind the log file growth problem. Another critical issue with log files and recovery modes is data safety. We haven’t addressed that subject in this article but We have in the past and we will undoubtedly discuss it again in a later newsletter.