Factors That Can Delay Log Truncation
Log truncation frees space in the log file for reuse by the transaction log. Because the active part of the log cannot be truncated or removed by shrinking, truncation can be delayed when log records remain active for a long time.
Note
For information about how log truncation works, see Transaction Log Truncation.
Log records can remain active under a variety of conditions, which are described in this topic. You can discover what, if anything, is preventing log truncation by using the log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view.
Note
Some of these factors, such as a very long-running transaction or a paused database mirroring session, can cause the transaction log to fill. For information about how to respond to a full transaction log, see Troubleshooting a Full Transaction Log (Error 9002).
The following table briefly describes the values of 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 |
Currently there are one or more reusable virtual log files. |
1 |
CHECKPOINT |
No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file (all recovery models). This is a routine reason for delaying log truncation. For more information, see Checkpoints and the Active Portion of the Log. |
2 |
LOG_BACKUP |
A log backup is required to move the head of the log forward (full or bulk-logged recovery models only). .gif "Note") Note
Log backups do not prevent truncation.
When the log backup is completed, the head of the log is moved forward, and some log space might become reusable. |
3 |
ACTIVE_BACKUP_OR_RESTORE |
A data backup or a restore is in progress (all recovery models). A data backup works like an active transaction, and, when running, the backup prevents truncation. For more information, see "Data Backup Operations and Restore Operations," later in this topic. |
4 |
ACTIVE_TRANSACTION |
A transaction is active (all recovery models).
|
5 |
DATABASE_MIRRORING |
Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database (full recovery model only). For more information, see "Database Mirroring and the Transaction Log," later in this topic. |
6 |
REPLICATION |
During transactional replications, transactions relevant to the publications are still undelivered to the distribution database (full recovery model only). For more information, see "Transactional Replication and the Transaction Log," later in this topic. |
7 |
DATABASE_SNAPSHOT_CREATION |
A database snapshot is being created (all recovery models). This is a routine, and typically brief, cause of delayed log truncation. |
8 |
LOG_SCAN |
A log scan is occurring (all recovery models). This is a routine, and typically brief, cause of delayed log truncation. |
9 |
OTHER_TRANSIENT |
This value is currently not used. |
Data Backup Operations and Restore Operations
Log truncation cannot happen during any backup or restore operation. In SQL Server 2005 and later versions, log backups can occur during a data backup. However, log truncation cannot occur during such log backups, because all of the transaction log must remain available to the data backup operation. If a data backup is preventing log truncation, canceling the backup might help the immediate problem.
For more information about log truncation, see Transaction Log Truncation.
Long-Running Active Transactions
An active transaction requires that the log remain active from the log record that contains the start of the transaction. For example, if the beginning and end of a transaction is controlled by the user, a typical cause of a long-running transaction is a user starting a transaction and then leaving while the transaction waits for a response from the user. In these cases, although the waiting transaction generates very little log itself, the transaction holds up log truncation and causes the log to grow large.
Note
For information about how to avoid long-running transactions, see Coding Efficient Transactions.
Database Mirroring and the Transaction Log
Database mirroring requires that each log record remain active until the principal server instance receives notification from the mirror server instance that the record has been written to disk on the mirror server. If the mirror server instance falls behind the principal server instance, the amount of active log space grows accordingly. In this case, you may need to stop database mirroring, take a log backup that truncates the log, apply that log backup to the mirror database (using WITH NORECOVERY), and restart mirroring.
Important
Furthermore, before you can start mirroring, if any additional log backups are taken after the required log backup, you must also manually apply every additional log backup (always using WITH NORECOVERY). After applying the latest log backup, you can start mirroring.
For more information, see Removing Database Mirroring and Setting Up Database Mirroring.
Transactional Replication and the Transaction Log
Merge replication and snapshot replication do not affect transaction log size, but transactional replication can. If a database includes one or more transactional publications, the log is not truncated until all transactions relevant to the publications have been delivered to the distribution database. If the transaction log is growing too large, and the Log Reader Agent is running on a scheduled basis, consider shortening the interval between runs or setting it run in continuous mode. If it is set to run in continuous mode (the default), ensure that it is running. For more information about how to check Log Reader Agent status, see How to: View Information and Perform Tasks for the Agents Associated With a Publication (Replication Monitor).
Additionally, if you have set the option 'sync with backup' on the publication database or distribution database, the transaction log is not truncated until all transactions have been backed up. If the transaction log is growing too large, and you have this option set, consider shortening the interval between transaction log backups. For more information about how to back up and restore databases involved in transactional replication, see Strategies for Backing Up and Restoring Snapshot and Transactional Replication.
To manage replication
To monitor replication