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).
|
| 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.
EmoticonEmoticon