We all must have seen log_reuse_wait_desc column from sys.databases catalog. But Log records can remain active under a variety of conditions, which are mentioned below and can delay log truncation...
But I bet you have missed all the values the log_reuse_wait_desc column can have....check it now!!
Description
0
NOTHING
Currently there are one or more reusable virtual log files.
1
CHECKPOINT
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).
When the log backup is completed, the head of the log is moved forward, and some log space might become reusable.
Note Log backups do not prevent truncation.
3
ACTIVE_BACKUP_OR_RESTORE
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. For more information, see "Long-Running Active Transactions," later in this topic.
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
2
LOG_BACKUP
A log backup is required to move the head of the log forward (full or bulk-logged recovery models only).
When the log backup is completed, the head of the log is moved forward, and some log space might become reusable.
Note Log backups do not prevent truncation.
3
ACTIVE_BACKUP_OR_RESTORE
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. For more information, see "Long-Running Active Transactions," later in this topic.
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 is a routine, and typically brief, cause of delayed log truncation.
9
OTHER_TRANSIENT
No comments:
Post a Comment