In a previous post (see here) I debunked a myth about how much transaction log a full backup would include. I had a question in the blog post comments that asked (paraphrasing):
The full backup has to include all the transaction log from the begin LSN of the oldest active transaction at the time the data read portion of the backup ends, until the LSN at which the data read portion ends. If that begin LSN is later in time than the LSN of the checkpoint that backup does initially, why does the full backup need to include all thr transaction log between the checkpoint and the begin LSN? What is it used for?
I replied in the comments with a quip that it would be easier to reply with a whiteboard and a timeline – so I got all enthusiastic and created a picture in Powerpoint to help explain better.
Consider the timeline in the picture above for a full backup (the red numbers match the list below):
-
The backup operation take a checkpoint to force all dirty pages in the buffer pool to disk – both those containing changes from transactions that have committed and those containing changes from transactions that are still in-flight. The backup operation then starts reading the allocated pages in the database.
-
The read operation reads page X
-
Transaction A starts
-
Transaction A makes a change to page X. The copy in the backup is now out-of-date. Note that the backup will not read page X again – it's already passed that point in the database.
-
Transaction B starts. It won't complete before the data read operation completes so it's begin LSN is the oldest active transaction begin LSN.
-
Transaction A commits. This commits the changes to page X.
-
The backup data read operation completes and transaction log reading starts.
Now, the reason that the transaction log is read is so that the restore operation can recover the database so it is transactionally consistent as of the point in time when the read data operation completed.
If the transaction log was only included from the oldest active transaction begin LSN (point 5), then the copy of page X that was restored from the backup (read at point 2) would not be updated with the changes from transaction A (that happened at point 4). This means that it would not be transactionally consistent with the rest of the database as of the time the read data operation completed (point 7).
So, (ignoring replication) the minimum LSN of the transaction log that's included in the full backup is MIN (LSN of last checkpoint, LSN of oldest active transaction). This ensures that recovery can REDO log records to bring pages up-to-date and UNDO log records for transactions that had not committed.
Much easier to explain with aid of a picture than without! :-)
9 Responses to More on how much transaction log a full backup includes
Great explaination Paul, it was indeed helpful
Hi Paul,
Thanks for this answer. Can we simplify the whole sketch by saying: when a full backup begins, there are only two situations:
- Either there is at least one active transaction at the time the backup begins: in this case, the start LSN will be the LSN of the oldest active xact.
- Either the database is quiesced (in your example above, the B transaction start after the backup checkpoint): in this case, the start LSN will be the LSN of the checkpoint triggered by the backup.
Come in Paris, I can get you the whiteboard !! ;-)
Chrz,
David.
Basically, yes. There are some rare twists but they’d just confuse things for no good reason.
Thanks
[...] More on how much transaction log a full backup includes [...]
[...] A full database backup only has to backup enough transaction log to allow the database to be restored and recovered to a transactionally consistent point. In other words, it only requires the transaction log back to the beginning of the oldest active transaction at the point that the data-reading section of the full backup completes. This is a source of immense confusion – many people don't believe that a full (or differential) backup needs to also backup some transaction log. For a more in-depth study of this, see my previous blog posts Debunking a couple of myths around full database backups and More on how much transaction log a full backup includes. [...]
[...] a full backup may still need some of that log so it can’t be cleared (see this post and this post for an explanation). The simple route was taken of disallowing concurrent log backups with [...]
[...] More on how much transaction log a full backup includes [...]
[...] view of the database. See Debunking a couple of myths around full database backups and More on how much transaction log a full backup includes for details if you don't believe [...]
[...] is a lot more complex than it sounds but Paul wrote a comprehensive post on exactly what this means here). Simply put, this requirement means that transaction log backups CAN occur concurrently; however, [...]