SQL Server log_reuse_wait_desc: why the transaction log will not truncate

The database is throwing error 9002, writes are failing, the log file has eaten the volume, and someone is about to add another log file or shrink the existing one. Before anyone touches file sizes, run one query:

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases;

That third column is the root-cause field most teams skip. It tells you why log truncation could not clear any Virtual Log Files (VLFs) the last time SQL Server tried. Every fix for a full log flows from this value. Adding disk space without reading it treats the symptom: the log fills again, usually within hours, and now you also have VLF fragmentation to deal with.

What this means

SQL Server uses write-ahead logging: every modification is written to the transaction log before it hits data files. The log is divided into VLFs, and space inside the log file can only be reused after the records in a VLF are no longer needed. What “no longer needed” means depends on the recovery model and on whatever consumer still needs those log records:

• FULL or BULK_LOGGED recovery: a VLF can only be cleared after a log backup captures it.
• SIMPLE recovery: a VLF can be cleared after a checkpoint.
• Always: no VLF can be cleared if an active transaction started inside it, if replication or Change Data Capture has not scanned it, or if an Availability Group secondary has not hardened it.

When truncation is blocked, the log grows. If autogrow is available it expands, expensively, since Instant File Initialization does not apply to log files, so new space is zero-initialized while all log-writing transactions pause. If autogrow is capped or the volume is full, the database stops accepting writes: error 9002.

log_reuse_wait_desc reports the reason truncation failed the last time it was attempted. One nuance: the value is a snapshot of the last attempt, not a live probe. If the blocking condition cleared but truncation has not been re-attempted, you can see a stale value (for example ACTIVE_TRANSACTION after the transaction has already committed). Issuing a CHECKPOINT or taking a log backup triggers a fresh attempt and refreshes the value.

flowchart TD
    A[Log filling or error 9002] --> B{log_reuse_wait_desc}
    B -->|LOG_BACKUP| C[Take a log backup; verify the log backup job runs]
    B -->|ACTIVE_TRANSACTION| D[Find the open transaction; commit, rollback, or kill]
    B -->|REPLICATION| E[Check replication agents and CDC capture job]
    B -->|AVAILABILITY_REPLICA| F[Check AG send queue and secondary health]
    B -->|CHECKPOINT| G[Run CHECKPOINT; simple recovery only]
    B -->|NOTHING| H[Truncation works; log is growing faster than it clears]
    C --> I[Re-check Percent Log Used]
    D --> I
    E --> I
    F --> I
    G --> I

Common causes

The common failure pattern: teams see the log filling, add space or shrink, and never read log_reuse_wait_desc. The cause is usually replication lag, a long transaction, or missing log backups, none of which space fixes.

Quick checks

Safe, read-only queries to run first.

-- 1. The root cause field, for every database
SELECT name, recovery_model_desc, state_desc, log_reuse_wait_desc
FROM sys.databases;

-- 2. Log usage per database (percent used plus the wait reason)
SELECT
    db.name,
    ls.cntr_value AS log_space_used_pct,
    db.log_reuse_wait_desc
FROM sys.dm_os_performance_counters ls
JOIN sys.databases db ON ls.instance_name = db.name
WHERE ls.counter_name = 'Percent Log Used'
  AND ls.object_name LIKE '%Databases%';

-- 3. Absolute log sizes
DBCC SQLPERF(LOGSPACE);

-- 4. Free space on the volumes holding log files
SELECT DISTINCT
    vs.volume_mount_point,
    vs.available_bytes / 1048576 AS available_mb,
    CAST(vs.available_bytes * 100.0 / vs.total_bytes AS DECIMAL(5,2)) AS pct_free
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
ORDER BY pct_free;

-- 5. Oldest open transaction in the affected database
-- Run in the database context that shows ACTIVE_TRANSACTION
USE YourDatabase;
DBCC OPENTRAN;

-- 6. Log backup freshness (FULL recovery databases)
SELECT
    d.name,
    MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS last_log_backup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs ON d.name = bs.database_name
WHERE d.recovery_model_desc = 'FULL'
GROUP BY d.name
ORDER BY last_log_backup;

-- 7. If AG is involved: send and redo queues (sizes and rate in KB)
SELECT
    ar.replica_server_name,
    DB_NAME(drs.database_id) AS database_name,
    drs.synchronization_state_desc,
    drs.log_send_queue_size AS send_queue_kb,
    drs.redo_queue_size AS redo_queue_kb,
    drs.redo_rate AS redo_rate_kb_per_s
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id;

-- 8. If REPLICATION shows but you removed replication: check CDC
SELECT name, is_cdc_enabled FROM sys.databases;

How to diagnose it

1. Read log_reuse_wait_desc for the affected database. This is step one, not step five. Everything below branches from it.

2. Confirm the log is actually filling versus filled. Check Percent Log Used and volume free space. Above 90% and rising with no autogrow headroom is an urgent, wake-someone-up situation; above 70% with a non-NOTHING wait reason needs investigation now.

3. If LOG_BACKUP: check the last log backup in msdb.dbo.backupset. Do not trust the Agent job’s “success” status; the job can succeed while writing to a bad target, and backupset is the authoritative record. A missing or broken log backup job on a FULL recovery database is the most common cause of log-full outages.

4. If ACTIVE_TRANSACTION: run DBCC OPENTRAN in that database to see the oldest active transaction, then find the owning session in sys.dm_exec_sessions. The dangerous variant is a session that is sleeping with an open transaction: an application returned a connection to the pool without committing, or someone left SSMS open with a BEGIN TRAN. It will not resolve on its own, and it often holds locks at the same time, so check for blocking chains too. If DBCC OPENTRAN shows nothing but the value persists, it may be a stale report from the last truncation attempt; run a CHECKPOINT (or log backup) and re-check.

5. If REPLICATION: check whether the log reader agent is running. If you never configured transactional replication, check is_cdc_enabled: Change Data Capture uses the same log scan mechanism, and a stopped or broken CDC capture job surfaces as REPLICATION. This “ghost replication” case is common after someone disables replication but leaves CDC behind.

6. If AVAILABILITY_REPLICA: the primary cannot truncate log that secondaries have not hardened and redone. Query sys.dm_hadr_database_replica_states for send queue and redo queue per secondary. A disconnected synchronous secondary or a secondary whose redo is drowning (CPU or I/O bound) both block truncation on the primary.

7. If CHECKPOINT: on a SIMPLE recovery database, issue a manual CHECKPOINT and confirm Percent Log Used drops. If this keeps recurring, something is preventing checkpoints from completing.

8. If NOTHING: truncation is working, but log generation outpaces clearing. The log file may be undersized for peak volume between log backups, or a bulk operation (index rebuild, large data load) is generating more log than usual. Check whether log backup frequency matches your write volume.

Metrics and signals to monitor

Fixes

LOG_BACKUP: take a log backup, then fix the job

The immediate fix is a log backup. After it completes, confirm Percent Log Used drops and log_reuse_wait_desc returns to NOTHING. One gotcha: immediately after a successful log backup, the value can still show LOG_BACKUP if the backed-up records all sat in the currently active VLF and zero VLFs were cleared. That is normal; the next backup clears it once the current VLF fills.

The durable fix is the log backup job itself. Verify it exists, runs on schedule, and actually writes rows to msdb.dbo.backupset. For FULL recovery production databases, a log backup every 15-60 minutes is typical depending on RPO. If the database does not need point-in-time recovery, consider whether SIMPLE recovery is the honest choice rather than FULL with broken backups.

ACTIVE_TRANSACTION: end the transaction, do not add space

Identify the session behind the oldest open transaction. If it is sleeping with an open transaction and no active request, it is an application bug or an abandoned session, and killing it is usually the right call after assessment. Two warnings: KILL forces a rollback that can take as long as the original transaction ran, and the same transaction may be holding locks, so expect a blocking backlog to drain once it clears. Never “fix” this by growing the log file; the transaction will just consume the new space too.

REPLICATION / AVAILABILITY_REPLICA: fix the consumer

For replication, restart or repair the log reader agent. For the CDC ghost case, either fix the capture job or, if CDC is genuinely not needed, remove it. For AG, the fix is on the secondary side: reconnect a disconnected replica, or relieve whatever is starving redo (secondary I/O bottleneck, CPU saturation, readable-secondary queries blocking redo). In synchronous commit, a struggling secondary also inflates commit latency on the primary via HADR_SYNC_COMMIT waits, so you are fixing two problems at once. Switching to asynchronous commit is possible emergency relief but carries data-loss risk on failover; treat it as a deliberate tradeoff, not a reflex.

CHECKPOINT: issue it, then ask why it was missing

A manual CHECKPOINT is safe and usually resolves this on SIMPLE recovery databases. If the value keeps coming back, look at what delays checkpoints: heavy write bursts, I/O saturation on data files, or an indirect checkpoint configuration that cannot keep up. With indirect checkpoint (TARGET_RECOVERY_TIME, 1 minute by default for databases created in SQL Server 2016 and later), dirty pages are written continuously in the background and log truncation only progresses as the checkpoint completes, so a busy SIMPLE-recovery database can hold CHECKPOINT until the flush catches up. There is no fixed threshold for this; the fix is to let the checkpoint finish or revisit target recovery time.

Emergency space relief (last resort only)

If the volume is full and error 9002 is already firing, the emergency path is: add a second log file on a different volume with free space, take a log backup to unblock truncation, then shrink the temporary file away. This buys time to execute the real fix above. It is not the fix.

What not to do: shrink the log as a first response. Shrinking without resolving log_reuse_wait_desc leads to immediate regrowth, and repeated shrink/regrow cycles create thousands of small VLFs, which slow recovery, backup, and restore. If sys.dm_db_log_info (SQL 2016 SP2+) or DBCC LOGINFO shows VLF counts in the thousands, plan a one-time consolidation: resolve the wait reason, shrink, and manually regrow the log in a small number of appropriately sized increments.

Prevention

• Alert on log_reuse_wait_desc, not just log percent. Percent used tells you the log is filling; the wait reason tells you it started being blocked hours ago. Alert when any database holds a non-NOTHING value across multiple samples.
• Monitor log backup freshness from msdb.dbo.backupset. Job success status is not proof of a backup. Page on repeatedly failing log backups with growing log usage.
• Pre-size log files. Autogrow should be a safety net, not a sizing strategy. Every log autogrow pauses all log-writing transactions because IFI does not apply to log files. Alert on autogrow events themselves.
• Track VLF count. Consolidate before it becomes a recovery-time problem discovered during an unplanned restart.
• Watch transaction age. Sleeping sessions with open transactions are future log-full outages and blocking cascades. Detect them before they are either.
• Watch AG queues as a truncation dependency. A falling-behind secondary is a primary log-full incident in waiting.

How Netdata helps

• Netdata collects per-database transaction log usage alongside log_reuse_wait_desc, so you see that truncation is blocked and why on the same timeline instead of querying DMVs mid-incident.
• Log backup freshness from msdb.dbo.backupset trends next to log growth, making the classic “backup job says success, backupset says nothing since Tuesday” failure visible immediately.
• AG send and redo queue metrics correlate secondary lag with primary log growth, which is the correlation that turns an AVAILABILITY_REPLICA wait from a mystery into a pointed question about one specific replica.
• Disk space on database file volumes is monitored with log usage, so you can see runway (free space versus growth rate) rather than a binary “volume full” alert.
• Per-second collection catches short-lived blocking transactions and autogrow stalls that a 5-minute poll misses entirely.

Netdata’s Microsoft SQL Server monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• How Microsoft SQL Server actually works in production: a mental model for operators
• Microsoft SQL Server monitoring checklist: the signals every production instance needs
• Microsoft SQL Server monitoring maturity model: from survival to expert
• SQL Server TempDB full: the shared scratch database that halts every query
• SQL Server wait statistics: reading sys.dm_os_wait_stats to find the real bottleneck
• SQL Server user connections climbing: connection pool leaks and retry storms
• SQL Server CPU utilization high: telling query load apart from a bad plan
• SQL Server CXPACKET and CXCONSUMER waits: parallelism, MAXDOP, and what is actually wrong
• SQL Server high compilations per second: plan cache pollution and CPU burn
• SQL Server runnable tasks backlog: the in-engine CPU queue OS metrics miss
• SQL Server THREADPOOL waits: worker thread exhaustion and refused connections
• SQL Server worker thread exhaustion: when the instance stops accepting work