SQL Server - Transaction log size
We quite often get questions about why the transaction log file is growing and how big it should be. 10% of the database size? 100MB? 100GB? A quick search on the Internet will yield many hits and "it depends" is often the default preamble to these. So what is it due to? Well, there's an equally fuzzy answer to that question; it depends on how quickly log entries are created and deleted in the transaction log file by the processes that internally use them in SQL Server.
All transactions create log entries in the transaction log file with information about what changes have occurred in the database. These are needed to keep the database consistent in case any problems occur. In some cases the logging can be minimized depending on the recovery model of the database and the SQL call made. The transaction log file must be purged of log entries so as not to become infinitely large, but the active log entries needed to recover the database can never be deleted.
SQL Server deletes the inactive log entries when the BACKUP LOG command is run for databases in the FULL or BULK_LOGGED recovery model or, if the database is in the simple recovery model, each time a checkpoint is made in the database. A checkpoint is, somewhat simplified, the last time all data in memory was written to disk and is handled normally automatically by SQL Server. Note that deleting the inactive log entries in the transaction log file does not shrink the physical file, it only deletes the contents.
The most common reasons we encounter when the transaction log cannot be cleared and continues to grow are that one or more long transactions are active or, for databases in the FULL or BULK_LOGGED recovery model, that no transaction log backup has been performed on them. Another reason why a transaction log file grows a lot for databases in the FULL or BULK_LOGGED recovery model may be the rebuilding of an index because it occurs in a transaction. So also all log entries for this have to fit in the transaction log file.
How big should the transaction log file be? Well, simply put, it needs to be large enough to contain all the log entries between two checkpoints or transaction log backups and to hold the largest index.
So don't rely on any answers with general sizes, it depends, and it's not easy to determine how big a transaction log file should be. It requires some work for each database in configuring backup intervals, index sizes etc. to arrive at a suitable transaction log file size and keep it there.
/Björn
