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 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 "ms345414.note(en-US,SQL.90).gif")
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).
- A long-running transaction might exist at the start of the log backup. In this case, freeing the space might require another log backup. For more information, see "Long-Running Active Transactions," later in this topic.
- A transaction is deferred (SQL Server 2005 Enterprise Edition and later versions only). A deferred transaction is effectively an active transaction whose rollback is blocked because of some unavailable resource. For information about the causes of deferred transactions and how to move them out of the deferred state, see Deferred Transactions.
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. When you take file backups, using WITH NO_LOG can help avoid the problem of preventing log truncation.
For more information about log truncation, see Transaction Log Truncation.
Important
The NO_LOG and TRUNCATE_ONLY options of the BACKUP LOG statement will be removed in a future version of SQL Server. These options remove the inactive part of the log without making a backup copy of it, and truncate the log by discarding all but the active log. This breaks the log chain. Until the next full or differential database backup, the database is not protected from media failure. Therefore, we strongly recommend that you avoid using either of these options in new development work and that you plan to modify applications that currently use it.
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, set it to 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
See Also
Concepts
Checkpoints and the Active Portion of the Log
Transaction Log Truncation
Shrinking the Transaction Log
Troubleshooting a Full Transaction Log (Error 9002)