How does a transaction log backup deal with active log?

Posted on

Question :

What happens to the log records that maintain any open uncommitted transactions when a transaction log backup starts? Will these records also be included in the log backup?

Answer :

Yes, active transactions are included in transaction log backups and this is how the database restore WITH NORECOVERY option works.

I think it’s also important to understand how database restore operations and options work with SQL Server and that too will help you get a better understanding of some of this.

Quoted explanations and some key points to help clarify further

1. Transaction Log Backup

A transaction log backup allows you to backup the active part of the
transaction log
. So after you issue a “Full” or “Differential” backup
the transaction log backup will have any transactions that were
created after those other backups completed. After the transaction
log backup is issued, the space within the transaction log can be
reused for other processes. If a transaction log backup is not taken,
the transaction log will continue to grow.

2. Log Backup Chain

A log backup chain is an unbroken series of log backups that contain
all the transaction log records necessary to recover a database to a
point in time. A chain starts with a full database backup, and
continues until something breaks the chain, thus preventing more log
backups being taken until another full (or differential) backup is

3. Truncating the Transaction Log

When SQL Server finishes backing up the transaction log, it
automatically truncates the inactive portion of the transaction log.
This inactive portion contains completed transactions and so is no
longer used during the recovery process. Conversely, the active
portion of the transaction log contains transactions that are still
running and have not yet completed. SQL Server reuses this truncated,
inactive space in the transaction log instead of allowing the
transaction log to continue to grow and use more space.

Additional Reading

Leave a Reply

Your email address will not be published. Required fields are marked *