4

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?

4

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 taken.


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.