SQL Server transaction log percent used climbing toward full

The Percent Log Used counter on one of your databases is climbing and it is not coming back down. This is the leading gauge before Error 9002 (“The transaction log for database ‘X’ is full”), at which point every write against that database fails. Reads may still work, which makes the outage look strange from the application side: queries succeed, inserts and updates throw errors.

The degradation curve is a cliff edge. The database works normally until the log hits 100%, then all writes block immediately. There is no gradual slowdown along the way, which is why this has to be caught on the gauge, not on user complaints.

SQL Server tells you exactly why the log cannot be truncated. The log_reuse_wait_desc column in sys.databases names the blocker, and each value maps to a specific fix. This guide walks the diagnosis in that order.

What this means

Every modification is written to the transaction log before it touches data files (write-ahead logging). The log is divided into Virtual Log Files (VLFs), and space is reused only after truncation:

  • In full or bulk-logged recovery, truncation happens after a log backup.
  • In simple recovery, truncation happens at checkpoint.

If something prevents truncation (an open transaction, a missing log backup, replication or AG lag), the log grows. If autogrow is enabled and the volume has space, it keeps growing until it hits a max size or fills the disk. If it cannot grow, writes stop. Log full is one of the most common causes of unexpected SQL Server outages, and it is preventable.

A database in simple recovery should rarely show high log usage. If it does, a long-running transaction is almost always active, because checkpoint truncation cannot reclaim space held by the oldest open transaction.

Common causes

CauseWhat it looks likeFirst thing to check
Missing log backups (full recovery model)log_reuse_wait_desc = LOG_BACKUP, steady climb between failed or disabled backup jobsLast log backup time in msdb.dbo.backupset
Long-running open transactionlog_reuse_wait_desc = ACTIVE_TRANSACTION, often with blocking as a side effectDBCC OPENTRAN in the database context
AG secondary behindlog_reuse_wait_desc = AVAILABILITY_REPLICA, send queue or redo queue growingsys.dm_hadr_database_replica_states queue sizes
Replication or CDC laglog_reuse_wait_desc = REPLICATION, log reader agent stopped or behindReplication agent status and latency
Runaway index rebuild or bulk operationRapid climb during maintenance, huge log generation in a short windowWhat is running now in sys.dm_exec_requests
Checkpoint not completing (simple recovery)log_reuse_wait_desc = CHECKPOINT on a simple recovery databaseWhether a long transaction is pinning the log anyway

Quick checks

These are all read-only and safe to run during an incident.

-- 1. Percent log used and truncation blocker, per database
SELECT
    db.name,
    ls.cntr_value AS log_space_used_pct,
    db.recovery_model_desc,
    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%'
ORDER BY ls.cntr_value DESC;

-- 2. Absolute log sizes (percentages can hide small vs huge logs)
DBCC SQLPERF(LOGSPACE);

-- 3. Oldest open transaction in the affected database
-- Run in the context of the affected database:
USE YourDatabase;
DBCC OPENTRAN;

-- 4. Free space on the volumes holding database 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. When did the last log backup actually finish?
SELECT database_name, MAX(backup_finish_date) AS last_log_backup
FROM msdb.dbo.backupset
WHERE type = 'L'
GROUP BY database_name
ORDER BY last_log_backup;

-- 6. VLF count (SQL 2016 SP2+). Run in the affected database.
SELECT COUNT(*) AS vlf_count FROM sys.dm_db_log_info(DB_ID());

How to diagnose it

Work this in order. The blocker value determines everything downstream.

flowchart TD
    A[Percent Log Used climbing] --> B{log_reuse_wait_desc?}
    B -->|LOG_BACKUP| C[Take a log backup now; fix the backup job]
    B -->|ACTIVE_TRANSACTION| D[DBCC OPENTRAN; assess then commit or kill]
    B -->|AVAILABILITY_REPLICA| E[Check send/redo queues and secondary health]
    B -->|REPLICATION| F[Check log reader agent status and latency]
    B -->|CHECKPOINT| G[Simple recovery: find the pinning transaction]
    B -->|NOTHING but still high| H[Log is oversized or truncated; shrink later if needed]
    C --> Z[Verify percent used drops]
    D --> Z
    E --> Z
    F --> Z
    G --> Z
  1. Confirm the trend, not just the level. Take two readings of Percent Log Used a minute apart. A stable 80% after a big batch job is very different from 80% and rising 2% per minute.
  2. Read log_reuse_wait_desc for the affected database. This is the root-cause column. NOTHING means truncation is possible and the log will be reused; anything else names the blocker.
  3. Check recovery model. Full recovery with no log backups is the number one cause. Simple recovery with high usage means a long transaction, almost without exception.
  4. Branch on the blocker:
    • LOG_BACKUP: check the backup job and msdb.dbo.backupset. If the database was recently created or switched to full recovery, the log chain may not be established yet; a full backup is needed before log backups can drive truncation.
    • ACTIVE_TRANSACTION: run DBCC OPENTRAN in that database, then find the session in sys.dm_exec_sessions / sys.dm_exec_requests. A sleeping session with an open transaction is the classic application bug.
    • AVAILABILITY_REPLICA: check log_send_queue_size, log_send_rate, redo_queue_size, and redo_rate in sys.dm_hadr_database_replica_states. A disconnected or slow secondary pins the log on the primary.
    • REPLICATION: check whether the log reader agent is running and how far behind it is.
  5. Check headroom before declaring victory or paging. If autogrow is enabled, what is the configured max size and the free space on the log volume (step 4 in quick checks)? A log at 90% with no autogrow headroom and a rising trend is an imminent write outage.
  6. Estimate runway. Seconds until full is approximately available log space divided by the current log generation rate (log bytes flushed per second). This is a community-derived calculation, not a Microsoft formula, and it only holds while truncation stays blocked. If log_reuse_wait_desc flips back to NOTHING, the runway resets.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
Percent Log Used per databaseThe leading gauge before Error 9002Above 70% with a non-NOTHING reuse wait
log_reuse_wait_descNames the exact truncation blockerAnything other than NOTHING sustained
Log bytes flushed/secLog generation rate; feeds runway estimationSustained high rate while usage climbs
Free space on log volumeAutogrow is useless if the volume is fullFree space less than the next growth increment plus margin
Autogrow eventsEach log autogrow pauses all log-writing transactions while space is zero-initializedAny event during business hours
VLF count per databaseThousands of VLFs slow recovery, backup, and restoreAbove 1000; severe above 10,000
AG send/redo queue sizeSecondary lag pins the primary’s logSustained growth
Time since last log backupDirect measure of the most common causeExceeds your RPO interval
Error 9002 in the error logThe failure itselfAny occurrence

Alert at 70% used with a non-NOTHING reuse wait (ticket), and page at 90% used with a rising trend and no autogrow or volume headroom. A log at 90% with plenty of autogrow headroom is a ticket, not a page: autogrow is expensive but automatic.

Fixes

Match the fix to the blocker. Adding disk space without clearing the blocker treats the symptom and buys hours at best.

LOG_BACKUP: take a log backup and fix the job

Take a log backup now; percent used should drop within moments of it completing. Then find out why the job stopped: disabled job, failing destination, full backup volume, or a database that was restored and never re-enrolled in the backup schedule. Do not trust the job’s reported status; verify rows in msdb.dbo.backupset, because a job can report success while writing to a bad target.

If the database does not need point-in-time recovery, switching to simple recovery is a legitimate fix, at the cost of log-based point-in-time restore.

ACTIVE_TRANSACTION: commit, or kill after assessment

Identify the session via DBCC OPENTRAN and sys.dm_exec_sessions. If it is an application connection that leaked an open transaction (sleeping, no active request), killing it is usually correct after you confirm what it was doing. Two warnings:

  • Rollback can take as long as the original transaction ran. Killing a 4-hour transaction is not instant relief.
  • Fix the application code afterward. A connection returned to the pool with an open transaction will recur.

AVAILABILITY_REPLICA: unblock the secondary

Check whether the secondary is connected and how large the send and redo queues are. Common root causes are network degradation between replicas and secondary I/O too slow to harden or redo. Resume suspended data movement if that is the issue. Switching to asynchronous commit relieves the primary in an emergency, at the cost of potential data loss on failover; treat that as a deliberate, time-boxed decision.

REPLICATION: restart or catch up the log reader

A stopped or failing log reader agent pins the log. Restart the agent and watch whether percent used drops as it catches up. If replication is permanently decommissioned, remove it properly rather than leaving the log pinned.

Last resort: add log space

If the disk is full and truncation is blocked, adding a second log file on a different volume provides emergency relief so writes can resume while you clear the blocker. This is explicitly a last resort. Instant File Initialization does not apply to log files, so new log space must be zero-initialized and all log-writing transactions pause during the growth. Pre-size the file and clean it up after the incident.

Do not shrink the log file while the blocker is active; the space cannot be reclaimed and you will churn VLFs. Shrink and regrow in controlled increments only after truncation is working, and only if the log is genuinely oversized for steady-state workload.

Prevention

  • Alert on the leading gauge, not the error. By the time Error 9002 appears, writes are already failing. Use the thresholds from the metrics table above.
  • Verify log backups from msdb.dbo.backupset, not job status. Alert when the last log backup exceeds your RPO interval for any full-recovery database.
  • Pre-size log files for peak volume between log backups (full recovery) or between checkpoints (simple recovery). Running above 50% used regularly means the log is undersized. Autogrow during business hours should be an alert, not a routine event.
  • Watch long-running transactions. An open transaction in simple recovery, or one that outlives a log backup interval in full recovery, is a future log-full incident. Monitor transaction age, not just count.
  • Track VLF count per database. Repeated small autogrowths fragment the log into thousands of VLFs, which slows recovery, backup, and restore. Above 1000, plan a controlled shrink and regrow.
  • Monitor AG and replication lag as log-space signals, not just availability signals. A lagging secondary is a log-full timer on the primary.
  • Batch large maintenance. Index rebuilds and bulk loads generate enormous log volume. Chunk them, and run log backups more frequently during the window.

How Netdata helps

  • Netdata collects Percent Log Used per database from the Databases performance object, so you see the climb and the trend over time rather than discovering it at Error 9002.
  • Per-database log metrics correlate directly with disk free space on the log volume, which is what turns “log is filling” into “log will stop writes in N minutes.”
  • Correlating log usage with connection and session activity helps distinguish a runaway transaction from a missing backup: usage climbing alongside one long-lived session points at ACTIVE_TRANSACTION; climbing with quiet sessions points at LOG_BACKUP.
  • For AG deployments, watching replication-related signals next to primary log usage makes the secondary-lag root cause visible without manual DMV queries mid-incident.
  • Historical retention matters here: log growth is often slow over days, and a trendline across a week separates steady-state growth from an acute incident.

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