Backups Causing Transaction Log Growth In Simple Mode

Andy Mallon explains why the transaction log will grow during a backup even if you’re in simple recovery mode:

When SQL Server begins backing up data pages, it also starts keeping track of transactions, via the transaction log. After it has backed up the last data page, it then also backs up all of the transactions that occurred during the data backup. Upon restore, it will then roll those transactions forward or backward, as necessary, to ensure a consistent image is restored.

In our librarian metaphor, she would keep an activity log, which would include the changes to books A and D from the first update, then also the changes to D, X, Y, and Z from the second update. She would not “fix” the data within the backup, but simply store those update details along with her mashed-up copy. In the unlikely event she had to recreate the books (ie, a restore), then she would go back and spend the effort to piece it back together. During that restore process, she would look at the first transaction and see that her copy of Book A in her backup was too old, but Book D already had the update, and she would roll forward the update to Book A. Next, she would process the second update and see that Books X, Y, and Z had the updates, but D still needed this second update, and she would roll forward that second update to Book D. At this point, she would have successfully reconstructed an image that is consistent to the time the backup completed.

Great metaphor to describe consistency during backups.

Related Posts

Checking Backup Encryption Size Differences

Tracy Boggiano has a script to check whether your backup file sizes are larger or smaller when they’re encrypted: I had a recent project to enable backup encryption on all our servers.  Then question from the storage team came up will this required additional space.  Well by then I had already enabled in all our […]

Read More

When Snapshots Begin

Kendra Little explains when a transaction really begins when you are in the snapshot isolation level: 00.000 – Session A sets its isolation level to snapshot 00.001 – Session A explicitly begins a transaction with BEGIN TRAN 00.002 – Session A starts a WAITFOR command for 15 seconds 10.000 – Before the WAITFOR completes, Session B […]

Read More


April 2017
« Mar May »