Factors That Can Delay Log Truncation
We all must have face problem at least once when we are not able to shrink our database log file. To find "Factors That Can Delay Log Truncation" the best possible solution given by Microsoft is DMV "sys.databases"
"select name as [database] ,log_reuse_wait , log_reuse_wait_desc from sys.databases"
This will return the reason and once we find root cause solution should not be a problem
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 .After that 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
4: ACTIVE_TRANSACTION : A long-running transaction might exist at the start of the log backup. In this case, freeing the space might require another log backup OR 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 (each log record remain active until the principal server instance receives notification from the mirror server instance ) ; Solution (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)
6: REPLICATION : During transactional replications only not during merge or snapshot, transactions relevant to the publications are still undelivered to the distribution database (full recovery model only).Solution (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)
7: DATABASE_SNAPSHOT_CREATION : A database snapshot is being created
8: LOG_SCAN : A log scan is occurring
9: OTHER_TRANSIENT : This value is currently not used
We all must have face problem at least once when we are not able to shrink our database log file. To find "Factors That Can Delay Log Truncation" the best possible solution given by Microsoft is DMV "sys.databases"
"select name as [database] ,log_reuse_wait , log_reuse_wait_desc from sys.databases"
This will return the reason and once we find root cause solution should not be a problem
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 .After that 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
4: ACTIVE_TRANSACTION : A long-running transaction might exist at the start of the log backup. In this case, freeing the space might require another log backup OR 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 (each log record remain active until the principal server instance receives notification from the mirror server instance ) ; Solution (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)
6: REPLICATION : During transactional replications only not during merge or snapshot, transactions relevant to the publications are still undelivered to the distribution database (full recovery model only).Solution (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)
7: DATABASE_SNAPSHOT_CREATION : A database snapshot is being created
8: LOG_SCAN : A log scan is occurring
9: OTHER_TRANSIENT : This value is currently not used
Good Info...Thanks for sharing
ReplyDeleteWelcome !!
Deletewhat about the Log_reuse_wait : 13 and its decription OLDEST_PAGE
ReplyDelete