Question: I have a weird problem that I hope you can explain. Sometimes I run updates to large amounts of data in one of my databases. While I’m doing so, the transaction log grows pretty large but my log backups aren’t the same size as the log. How can there be stuff in the log that isn’t being backed up?
Answer: You’re right that this seems non-intuitive but it’s actually an incredibly important behavior that helps protect your database from becoming suspect.
Some of the background for this answer I’ve discussed on this blog before. Whenever the database is changed, transaction log records are generated that describe the changes that are made. If something goes wrong with the transaction, it will roll back automatically (or you may choose to roll it back if you’re using explicit BEGIN TRAN/COMMIT TRAN statements).
Related: The Curious Case of the un-droppable trasaction log file
When a transaction rolls back, all changes it has made to the database must be undone again—by making further changes to the database. The undo changes must be logged in exactly the same way as the ‘forward’ part of the transaction—generating a log record for each change that is made as part of the roll back. (As an aside, these log records are described as ‘compensation’ log records, as the change they describe is compensating for the ‘forward’ log record.)
These compensation log records require space in the transaction log. What happens if the space is not available in the transaction log? The transaction would not be able to roll back completely, rendering the database transactionally inconsistent. If this happens, the database gets thrown offline in the SUSPECT state. This is clearly not the desired behavior.
SQL Server protects against this by reserving free space in the transaction log as it goes along. Specifically, whenever a transaction generates a log record, log space is reserved to ensure that the compensation log record will be able to be generated without having to physically grow the transaction log. This ensures that transactions can always roll back correctly, even if the log cannot grow, and the situation I described in the previous paragraph cannot happen.
This means that the log may grow to accommodate this reserved space when a transaction occurs that generates a lot of log records. In these cases, a transaction log backup will behave as normal—backing up all the transaction log records generated since the last log backup. However, it does not back up all the empty, reserved space as there’s no need to. This is the behavior you’re seeing.
I wrote a blog post back in 2008 that goes into this in more depth and provides an example script using DBCC SQLPERF (‘LOGSPACE’) to illustrate the behavior.