Log Reuse Waits Explained: ACTIVE_BACKUP_OR_RESTORE

From:http://sqlity.net/en/1835/log-reuse-waits-explained-active_backup_or_restore/

Introduction

There are eight reasons SQL Server might report when it cannot truncate the transaction log. Any one of these reasons results in a growing log file. This short series is looking at each of them in detail, explaining what is causing it and what you can do to resolve it. Today's log reuse wait reason is: ACTIVE_BACKUP_OR_RESTORE

Backup and Restore Consistency

SQL Server is first and foremost an ACID compliant database management system. That means that the rules dictated by this set of properties are adhered to under all circumstances. The ACID acronym stands for Atomicity, Consistency, Isolation and Durability. Without going into any detail, they guarantee among other things that a transaction that was committed does not suddenly disappear or worse partially disappear. In the context of backups and restores that means that after I restore from a backup all the data within that database must also be transactionally consistent.

One way to achieve backup consistency is to take the database offline before each backup and bring it online afterwards. That way no concurrent changes to the data can happen that would only be partially captured by the backup. However that is clearly not a desirable solution as, depending on the size of the database, a backup might take a considerable amount of time.

Another way is to record all transactions that are happening during the backup and then run a standard crash recovery using this data after every restore. This is the approach SQL Server takes. All transaction log records of transactions that were active at any time during the backup operation are included in the backup at the end of its run. That way SQL Server has enough information to undo the changes of transactions that were still active at the end of the backup and redo the changes of transactions that were committed during the backup.

Backup Wait

Because the log records are captured at the end of the backup operation, the virtual log files containing transaction log records of transactions that were active at any time during the backup cannot be reused until that last phase of the backup process has finished.

Restore Wait

At the end of any restore, SQL Server has to run crash recovery on the restored data. That means that during that time log reuse has to be prevented too. In most cases a database won't be online during its restore and this will therefore not be a problem. However, if you did a partial restore of only the active file groups of your database, you can bring those online before starting the restore of that large file group with the archive data. During that restore you will also not be able to reuse your log.

Resolving the ACTIVE_BACKUP_OR_RESTORE Wait

If SQL Server runs out of transaction log space during a backup or restore operation, the log_reuse_wait_desc column for that database will return a value of ACTIVE_BACKUP_OR_RESTORE.

In either case you have to wait for the operation to finish. That means if your backup strategy involves taking full or differential backups during a busy time, you have to give the transaction log enough room to grow. This is particularly important if the database is large, causing backup operations to potentially run for a long time.

You also have to account for transaction log growth if your restore strategy includes partial restores while other parts of the database are online.

Summary

At the end of each backup SQL Server captures the piece of the transaction log that covers all transactions that were active at any time during the backup. Because of that the virtual log files containing those transaction log records can't be reused during a backup. SQL Server will return a log_reuse_wait_desc value of ACTIVE_BACKUP_OR_RESTORE if it runs out of virtual log files during that time. During a restore operation the log cannot be reused either as the log records are required for the crash recovery process that is part of each restore operation. This is true too for database that are partially online during a restore operation.