SQL SERVER – Factors that can delay log truncation.

The process of log truncation frees space in the log files for reuse by transaction log. Exists factors that can delay lo truncation of the active portion by shrinking or truncated the log.

To check what are the factors that delay the log truncation we can check the sys.databases view, look into the columns: log_reuse_wait and log_reuse_wait_desc.

select log_reuse_wait, log_reuse_wait_desc from sys.databases

The following table shows a brief description of log_reuse_wait and log_reuse_wait_desc columns of the sys.databases 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).
2	LOG_BACKUP	A log backup is required to move the head of the log forward (full or bulk-logged recovery models only). 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. Is necessary to take a log frequently
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.
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. 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.
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). .
6	REPLICATION	During transactional replications, transactions relevant to the publications are still undelivered to the distribution database (full recovery model only).
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.

References.

MSDN. http://msdn.microsoft.com/en-us/library/ms345414.aspx