Home » Blog » T-SQL Tuesday #25 – SQL Server Tips & Tricks

T-SQL Tuesday #25 – SQL Server Tips & Tricks

2011-12-13 - General, T-SQL Tuesday

T-SQL Tuesday #25 is hosted by Allen White (blog). This month’s topic is “SQL Server Tips and Tricks”.

Transaction Log Reuse Wait

One question that keeps coming up in forums, at user group meetings and on twitter is: Why is my transaction log growing out of bounds?

To adhere to the ACID properties, SQL Server records all changes to the database first into the transaction log file. A transaction can only be committed after that write succeeds. The changes to the data itself actually get applied only to the data pages in the buffer pool. If and when they get written to the disk is influenced by several factors that are independent of the transaction itself. The records in the transaction log allow SQL Server to redo a change after a crash for example that prevented the data page changes to be written to disk.

That means in theory, that after the transaction is committed and after the data pages are preserved on disk, there is no need for SQL Server to hold on to the transaction log data anymore. That's why the transaction log usually does not grow unbounded, because SQL Server can reuse the parts of the file(s) that are not needed anymore.

In practice however, there can be several reasons, why such a reuse is not possible. The most common one is the requirement for log backups. If you have your database set to recovery mode FULL, SQL Server does not reuse any part of the log file until it is backed up with a transaction log backup. There are several other reasons why SQL Server might have to wait before it can reuse the transaction log, ranging from long running open transactions to transactional replication.

If you are in a situation that requires you to find out why the transaction log file keeps growing and growing, this simple query can give you the answer:

[sql] SELECT name,log_reuse_wait_desc FROM sys.databases;
[/sql]

The log_reuse_wait_desc column contains the reason why the SQL Server currently can't reuse the log file of that database. For an explanation of each of the eight reasons, check out my new article series here:

Eight Reasons why your Transaction Log Files keep growing