Factors that cause log truncation delay (log_reuse_wait columns and LOG_REUSE_WAIT_DESC columns in the sys.databases catalog view)

Reprint: Http://technet.microsoft.com/zh-cn/library/ms345414%28zh-tw,SQL.90%29.aspx

Log truncation frees up space in the log file for the transaction log to be reused. Because the active portion of the log cannot be truncated or deleted by shrinking, truncation is deferred when the log record remains active for a long time.

Attention:
For information about how log truncation works, see transaction log truncation.

Logging can remain active in many cases, as described in this topic. You can find the reason for blocking log truncation (if it exists) by using the log_reuse_wait and Log_reuse_wait_desc columns of the sys.databases catalog view in the following ways.

Attention:
Some of these factors, such as long-running transactions or paused database mirroring sessions, can cause the transaction log to fill up. For information about how to respond to a full transaction log, see Resolving a problem with a full transaction log (Error 9002).

The following table describes the values for the log_reuse_wait and log_reuse_wait_desc columns of the sys.database catalog view.

log_reuse_wait Value	Log_reuse_wait_desc Value	Description
0	Nothing	There is currently one or more reusable virtual log files.
1	CHECKPOINT	No checkpoint has occurred since the last log truncation, or the log header has not been moved across a virtual log file (all recovery modes). This is a common cause of log truncation latency. For more information, see checkpoints and the active portion of the log.
2	Log_backup	Requires a log backup to move the log head (for the full or bulk-logged recovery model only). Log backups do not block truncation. Attention: When the log backup is complete, the log headers are moved forward and some log space may become available for reuse.
3	Active_backup_or_restore	Data backup or restore is in progress (all recovery modes). The data backup works the same as the active transaction, which blocks truncation when the data backup runs. For more information, see the "Data backup operations and restore Operations" section later in this topic.
4	Active_transaction	The transaction is active (all recovery modes). There may be long-running transactions at the beginning of a log backup. In this case, freeing up space may require additional log backups. For more information, see the section "Long-running Active Transactions" later in this topic. Transactions will be deferred (only for SQL Server Enterprise Edition and later). A deferred transaction is actually an active transaction whose rollback is blocked because some resources are not available. For information about the causes of transaction delays and how to get them out of the deferred state, see deferred transactions.
5	Database_mirroring	Database mirroring is paused, or in high-performance mode, the mirror database is significantly lagging behind the principal database (the full recovery model only). For more information, see the "Database Mirroring and Transaction Logs" section later in this topic.
6	REPLICATION	Transactions related to the publication are still not delivered to the distribution database (the full recovery model only) during transactional replication. For more information, see the "Transactional Replication and transaction log" section later in this topic.
7	Database_snapshot_creation	Creating database snapshots (all recovery modes). This is a common cause of log truncation latency, and is often the primary cause.
8	Log_scan	A log scan is in progress (all recovery modes). This is a common cause of log truncation latency, and is often the primary cause.
9	Other_transient	This value is not currently in use.

Data backup operations and restore operations

Log truncation does not occur during any backup or restore operation. In SQL Server 2005 and later, log backups can occur during a data backup. However, log truncation does not occur during this type of log backup, because all transaction logs must be available for data backup operations. If a data backup prevents log truncation, canceling the backup may help resolve the problem that is directly caused by the backup. Using the WITH No_log when making a file backup can help avoid problems that prevent log truncation.

For more information about log truncation, see transaction log truncation.

Important NOTE:

Future versions of SQL Server will remove the no_log and TRUNCATE_ONLY options for the BACKUP LOG statement. These options delete the inactive log section and truncate the log without backing up the log copy, by discarding all logs except the active log. This interrupts the log chain. You will not be able to provide media failure protection for your database until the next full or differential database backup. Therefore, it is strongly recommended that you avoid using these two options in your new development work, and that you plan to modify the applications that currently use both options.

Long-running Active transactions

The active transaction requires the log to remain active from the beginning of the log record that contains the transaction. For example, if the start and end of a transaction is controlled by the user, the general reason for the long-running transaction is that the user leaves after the transaction is started, and the transaction waits for the user's response. In these cases, although a transaction in the waiting state generates a minimal log, it still blocks log truncation and causes the log to become larger.

Attention:
For information about how to avoid long running transactions, see writing a valid transaction.

Database mirroring and transaction logs

Database mirroring requires that each log record remain active until the principal server instance receives notification from the mirror server instance that the record was written to the mirrored server disk. If the mirror server instance lags behind the principal server instance, the amount of activity log space will increase accordingly. In this case, you might want to stop database mirroring, perform a log backup of the truncated log, apply the log backup to the mirrored database (using with NORECOVERY), and then restart the mirror.

Important NOTE:
In addition, if you perform any additional log backups after the necessary log backups, you must also manually apply each of the other log backups (always using with NORECOVERY) before starting mirroring. After you apply the most recent log backup, you can begin mirroring.

For more information, see Removing Database Mirroring and setting database mirroring. transactional replication and transaction logs

Merge replication and snapshot replication do not affect the size of the transaction log, but transactional replication affects. If the database includes one or more transactional publications, the log is truncated only after all transactions related to those publications are passed to the distribution database. If the transaction log becomes too large and the Log Reader Agent runs at a predetermined time interval, consider shortening the interval between its operations. Alternatively, set it to run in continuous mode. If you set it to run in continuous mode (the default), make sure it is running. For more information about how to check the status of Log Reader agents, see how to view information about the agents associated with a publication and perform tasks for this agent (Replication Monitor).

In addition, if you set the option "Sync with Backup" in the publication database or the distribution database, the transaction log will not be truncated until all transactions have been backed up. If the transaction log is growing too large, and you have set this option, consider shortening the interval between transaction log backups. For more information about how to back up and restore a database that involves transactional replication, see Backup and Restore Policies for snapshot replication and transactional replication.