Posted on Leave a comment

Log Reuse Waits

   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
Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.