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

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. Until the first full backup exists, a full-recovery database truncates the log at checkpoint like a simple-recovery database (“pseudo-simple” behavior) and log_reuse_wait_desc typically shows NOTHING.
   • 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

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.

Related guides

• SQL Server TempDB full: the shared scratch database that halts every query
• SQL Server user connections climbing: connection pool leaks and retry storms
• SQL Server wait statistics: reading sys.dm_os_wait_stats to find the real bottleneck
• SQL Server THREADPOOL waits: worker thread exhaustion and refused connections
• SQL Server CPU utilization high: telling query load apart from a bad plan
• SQL Server high compilations per second: plan cache pollution and CPU burn
• SQL Server CXPACKET and CXCONSUMER waits: parallelism, MAXDOP, and what is actually wrong
• SQL Server runnable tasks backlog: the in-engine CPU queue OS metrics miss
• SQL Server worker thread exhaustion: when the instance stops accepting work
• 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