SELECT name
, log_reuse_wait_desc
FROM sys.databases;
- NOTHING – vlfs available
- CHECKPOINT – dirty pages in buffer pool, awaiting checkpoint, issue checkpoint and/or adjust RECOVERY INTERVAL setting
- LOG_BACKUP – waiting for next transaction log backup
- ACTIVE_BACKUP_OR_RESTORE – transaction log backup in progress or is “hung”
- ACTIVE_TRANSACTION – open transaction, use DBCC OPENTRAN to identify
- DATABASE_MIRRORING – secondary falls behind
- REPLICATION – subscriber falls behind
- DATABASE_SNAPSHOT_CREATION – transient; snapshot creation in progress
- LOG_SCAN – transient; fn_dblog, during a checkpoint initiates a log scan to synchronize log sequence numbers, or other process that causes a log scan
- OTHER_TRANSIENT – currently not in use