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