DBA Scripts: Get Log Reuse Waits

Part of the DBA-Tools Project.

---

The One Question That Actually Matters

A transaction log that won’t stop growing, or a log backup job that suddenly can’t keep up, always comes back to the same underlying question: what is this database’s log actually waiting on before it can reuse space? SQL Server tracks the answer directly, log_reuse_wait_desc, one value per database, and it’s almost always the fastest route to a real answer.

This script reads that value across every online database on the instance, alongside recovery model, current log size, and how long since the last log backup, so you get the reason and the context in one pass.

---

Why Log Reuse Waits Matters

The transaction log can only reuse space once SQL Server is sure it no longer needs the oldest entries, and log_reuse_wait_desc tells you exactly what’s holding that up:

• LOG_BACKUP is by far the most common reason: a FULL or BULK_LOGGED database whose log can’t truncate until a log backup runs. No log backup job, or a job that stopped working, and the log just grows
• ACTIVE_TRANSACTION means something is actively holding the log open, a long-running batch, or a transaction that got left open and forgotten. Get Open Transactions is the next script to run when you see this
• AVAILABILITY_REPLICA, DATABASE_MIRRORING, and REPLICATION all mean a downstream consumer, an AG secondary, a mirror, a replication agent, hasn’t caught up yet, so the log can’t be reused even if the primary side is otherwise healthy
• A log that can’t reuse space keeps growing on disk until something changes, and a full log drive takes the database down, not just the log file

---

When to Run This Script

• Any time a transaction log file is unexpectedly large or still growing
• Routine SQL Server health checks
• Immediately after noticing a log backup job has failed or been skipped
• Before investigating blocking or a “slow” database, since log growth pressure can itself cause slowdowns

---

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-LogReuseWaits
Category    : monitoring
Purpose     : Reports why each database's transaction log cannot truncate and reuse
              space — the log_reuse_wait_desc reason per database, with recovery model,
              log size, and last log backup for context. This is the first question to
              answer when a log file is full or growing and won't shrink.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-log-reuse-waits/)
Requires    : VIEW ANY DATABASE, db_datareader on msdb
Notes       : NOTHING / CHECKPOINT are healthy. LOG_BACKUP means the FULL/BULK_LOGGED
              log is waiting on a log backup (the most common cause of a full log).
              ACTIVE_TRANSACTION points at a long-running or orphaned transaction —
              run Get-OpenTransactions next. AVAILABILITY_REPLICA / DATABASE_MIRRORING /
              REPLICATION mean a partner or agent hasn't consumed the log yet.
*/
-- Blog: https://sqldba.blog/dba-scripts-get-log-reuse-waits/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

;WITH LogBackup AS
(
    SELECT
        database_name,
        MAX(backup_finish_date) AS last_log_backup
    FROM msdb.dbo.backupset
    WHERE type = 'L'
    GROUP BY database_name
)

SELECT
    d.name                                              AS database_name,
    d.recovery_model_desc                               AS recovery_model,
    d.log_reuse_wait_desc                               AS log_reuse_wait,
    CAST(SUM(CAST(mf.size AS BIGINT)) * 8.0 / 1024
        AS DECIMAL(18,1))                               AS log_size_mb,
    lb.last_log_backup,
    CASE
        WHEN lb.last_log_backup IS NULL THEN NULL
        ELSE DATEDIFF(MINUTE, lb.last_log_backup, GETDATE())
    END                                                 AS minutes_since_last_log_backup,
    CASE d.log_reuse_wait_desc
        WHEN 'NOTHING'                  THEN 'OK'
        WHEN 'CHECKPOINT'               THEN 'OK'
        WHEN 'LOG_BACKUP'               THEN 'ACTION: take a log backup'
        WHEN 'ACTIVE_TRANSACTION'       THEN 'INVESTIGATE: long-running or orphaned transaction'
        WHEN 'ACTIVE_BACKUP_OR_RESTORE' THEN 'MONITOR: backup or restore in progress'
        WHEN 'AVAILABILITY_REPLICA'     THEN 'INVESTIGATE: AG secondary has not hardened the log'
        WHEN 'DATABASE_MIRRORING'       THEN 'INVESTIGATE: mirroring partner is behind'
        WHEN 'REPLICATION'              THEN 'INVESTIGATE: replication has not drained the log'
        WHEN 'OLDEST_PAGE'              THEN 'MONITOR: indirect checkpoint / oldest dirty page'
        WHEN 'XTP_CHECKPOINT'           THEN 'MONITOR: in-memory OLTP checkpoint'
        ELSE 'REVIEW'
    END                                                 AS status
FROM sys.databases AS d
JOIN sys.master_files AS mf
    ON mf.database_id = d.database_id
    AND mf.type_desc = 'LOG'
LEFT JOIN LogBackup AS lb
    ON lb.database_name = d.name
WHERE d.state_desc = 'ONLINE'
  AND d.database_id > 4
GROUP BY
    d.name,
    d.recovery_model_desc,
    d.log_reuse_wait_desc,
    lb.last_log_backup
ORDER BY
    CASE WHEN d.log_reuse_wait_desc = 'NOTHING' THEN 1 ELSE 0 END,
    log_size_mb DESC;

The status column translates the raw wait description into a plain next action, so you don’t need to keep the full list of log_reuse_wait_desc values memorized to know what to do next.

---

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Check why every database's log is or isn't reusing space:
.\run.ps1 Get-LogReuseWaits

# To run against a remote sql server:
.\run.ps1 Get-LogReuseWaits -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/monitoring/disk-space/Get-LogReuseWaits.sql
• powershell/wrappers/monitoring/disk-space/Get-LogReuseWaits.ps1

---

Example Output

This is a genuine finding on this lab instance, not staged: several databases are stuck on LOG_BACKUP, some for months.

database_name	recovery_model	log_reuse_wait	log_size_mb	minutes_since_last_log_backup	status
GrowthLab	FULL	LOG_BACKUP	1416.0		ACTION: take a log backup
RandomLab	FULL	LOG_BACKUP	72.0	240597	ACTION: take a log backup
WatchtowerMetrics	SIMPLE	NOTHING	1322.2	240597	OK
DemoDatabase	FULL	NOTHING	512.0		OK

---

Understanding the Results

• log_reuse_wait = NOTHING — nothing is blocking log space reuse right now. DemoDatabase above shows this even though it has never had a log backup, NOTHING/CHECKPOINT only describe the log’s current internal state, not backup posture. Pair this script with Recovery Model Audit for the full picture, a database can show NOTHING here today and still be one bad transaction away from a full log with no backup chain behind it.
• log_reuse_wait = LOG_BACKUP — the single most common finding. GrowthLab and RandomLab above are both FULL recovery with no recent log backup, so the log can grow indefinitely until one runs.
• minutes_since_last_log_backup blank — no log backup has ever been taken for that database, worse than merely stale.
• log_size_mb — sort by this to find where the LOG_BACKUP finding actually matters, a 10MB test database stuck on LOG_BACKUP is background noise, a 1.4GB one growing daily is not.

---

Common Causes

• A log backup job that was never created, or was created and later disabled without anyone noticing
• A database switched to FULL recovery for a one-off requirement and never given a log backup job to match
• An AG secondary or replication subscriber falling behind, holding the log open on the primary even though local backups are running fine

---

Best Practices

• Any database showing LOG_BACKUP with FULL or BULK_LOGGED recovery needs either a working log backup job or a deliberate switch to SIMPLE recovery, not silence
• Check this alongside Recovery Model Audit, a clean log_reuse_wait today doesn’t mean the backup chain behind it is healthy
• If the wait reason is AVAILABILITY_REPLICA or REPLICATION, the fix is on the downstream side, not the log itself, don’t just add more log backups and hope

---

Related Scripts

You may also find these scripts useful:

• Storage and Capacity (hub)
• Autogrowth History
• Database Files Detail
• Database Free Space Summary
• Database Growth Risk and Forecast
• Database Sizes and Free Space
• Disk Space
• Filegroup Space
• Open Transactions
• Recovery Model Audit
• Transaction Log Size and Usage

---

Frequently Asked Questions

Why does a database show LOG_BACKUP even though I just took a full backup?

A full backup doesn’t truncate the transaction log for FULL or BULK_LOGGED recovery, only a log backup does. If you only ever run full backups on a FULL-recovery database, the log will always show LOG_BACKUP.

Is it safe to just switch a stuck database to SIMPLE recovery?

Only if you don’t need point-in-time restore for it. SIMPLE recovery removes the LOG_BACKUP wait entirely since the log truncates on checkpoint, but you lose the ability to restore to any point other than your last full or differential backup.

---

Summary

log_reuse_wait_desc answers the single most useful question about a growing transaction log: what is actually stopping it from reusing space. Most of the time the answer is LOG_BACKUP, a missing or broken log backup job, and the fix is straightforward once you know it’s there.

Run this script whenever a log file looks larger than expected, and pair it with Recovery Model Audit for the backup-posture half of the same question.