SQL Server Error 9002: the transaction log for the database is full

Your application is throwing write failures and the SQL Server error log shows: “The transaction log for database ‘X’ is full due to ‘LOG_BACKUP’” (or ACTIVE_TRANSACTION, AVAILABILITY_REPLICA, REPLICATION, or another reason in quotes). Every INSERT, UPDATE, and DELETE against that database now fails with error 9002. Read-only queries may still work, which makes the outage look confusingly partial from the outside.

This is the most common unexpected SQL Server outage, and it is preventable. It is also frequently mishandled: the instinctive responses, “add more disk space” and “shrink the log,” treat the symptom while the log keeps growing. The log is full because it cannot be truncated, and the reason it cannot be truncated is stated explicitly in sys.databases.log_reuse_wait_desc. Read that first, fix that, then deal with the space.

One escalation to check immediately: if the affected database is TempDB, every database on the instance is affected, because TempDB is shared. TempDB uses the simple recovery model and cannot be log-backed-up, so the response is different. That case is covered below.

What this means

SQL Server uses write-ahead logging: every modification is written to the transaction log before it is written to data files. Log space is reused only after the records in it are no longer needed. In the full or bulk-logged recovery model, that means a log backup must run. In simple recovery, a checkpoint truncates the log. Regardless of recovery model, truncation can also be held up by an open transaction, a replication or AG pipeline that has not consumed the log, or other engine conditions.

When the log cannot be truncated, it grows until it hits its max size or the volume fills. At that point the engine raises error 9002 and all writes to the database stop. The database stays online. There is no gradual degradation curve here: it works until it does not.

The suffix in the error message (“due to ‘LOG_BACKUP’”) is the value of log_reuse_wait_desc at the time the error was raised. That value tells you which subsystem is blocking truncation, and each one has a different fix.

Common causes

Quick checks

Run these before changing anything. All are read-only.

-- 1. Why can't the log truncate? This is the first and most important query.
SELECT name, recovery_model_desc, log_reuse_wait_desc, state_desc
FROM sys.databases
WHERE name = 'YourDatabase';  -- repeat for tempdb if blast radius is instance-wide

-- 2. How full is the log, per database?
DBCC SQLPERF(LOGSPACE);
-- Returns: Database Name, Log Size (MB), Log Space Used (%), Status

-- 3. Percent log used with the reuse reason, in one view:
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%';

-- 4. Oldest open transaction (run in the affected database's context):
USE YourDatabase;
DBCC OPENTRAN;

-- 5. When was the last log backup?
SELECT database_name, MAX(backup_finish_date) AS last_log_backup
FROM msdb.dbo.backupset
WHERE type = 'L' AND database_name = 'YourDatabase'
GROUP BY database_name;

-- 6. Free space on the volumes hosting 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;

-- 7. If this is an AG primary: which secondary is holding up truncation?
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
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id;

How to diagnose it

Work through these in order. The flow below is the whole diagnostic in one picture.

flowchart TD
    A[Error 9002 raised] --> B{Which database?}
    B -->|tempdb| T[Instance-wide impact.
Add TempDB log space.
Find long transactions]
    B -->|user database| C[Read log_reuse_wait_desc
from sys.databases]
    C -->|LOG_BACKUP| D[Take a log backup now.
Two backups if never backed up.
Then fix the backup job]
    C -->|ACTIVE_TRANSACTION| E[DBCC OPENTRAN.
Identify session.
KILL if safe]
    C -->|AVAILABILITY_REPLICA| F[Check send and redo queues.
Fix or resume secondary]
    C -->|REPLICATION| G[Check Log Reader Agent.
Restart or fix distribution]
    C -->|OTHER / volume full| H[Emergency: add log file
on another volume]
    D --> Z[Verify: log_reuse_wait_desc = NOTHING
Percent Log Used falling]
    E --> Z
    F --> Z
    G --> Z
    H --> Z

1. Confirm scope. Is it one user database or TempDB? Check sys.databases state and DBCC SQLPERF(LOGSPACE) across all databases. TempDB full means instance-wide impact and a different playbook.

2. Read log_reuse_wait_desc. Do not skip this. Every remediation path branches from this value. Also note recovery_model_desc: a database in SIMPLE recovery showing high log usage almost always means a long open transaction, since checkpoints otherwise truncate continuously.

3. Branch on the reason. LOG_BACKUP, ACTIVE_TRANSACTION, AVAILABILITY_REPLICA, and REPLICATION each have a specific fix (next section). CHECKPOINT and ACTIVE_BACKUP_OR_RESTORE are usually transient: a checkpoint is pending or a backup is running. Recheck in a minute before intervening.

4. Check volume free space independently. Even after you fix the truncation blocker, a volume at zero free can leave you wedged: the log needs room to operate and any pending growth will fail. Use the volume query in Quick checks.

5. Verify recovery, not just the fix. After your intervention, log_reuse_wait_desc should return to NOTHING and Percent Log Used should start falling. If log_reuse_wait_desc still says LOG_BACKUP right after you took a log backup, issue a manual CHECKPOINT in the database and take another log backup; indirect checkpoint timing can leave the reuse reason stale after the first backup.

Metrics and signals to monitor

Fixes

LOG_BACKUP: take a log backup, then fix the job

Take a transaction log backup of the affected database immediately. If the log has never been backed up, a single backup may not truncate the log; the backup chain needs to be established first, so take a second log backup if the first one does not move Percent Log Used.

Then find out why the backup job stopped. Check the SQL Agent job history, the backup target volume (full target disks are a classic cause), and whether the job was disabled during maintenance and never re-enabled. Taking a manual backup restores writes but does nothing to stop the recurrence.

If the database does not actually need point-in-time recovery, switching to the simple recovery model is a legitimate permanent fix. That is a recovery-point-objective decision, not a cosmetic one: with simple recovery you lose the ability to restore to an arbitrary point in time. Do not switch recovery models just to stop alerts.

ACTIVE_TRANSACTION: find and kill the open transaction

Run DBCC OPENTRAN in the database context to identify the oldest active transaction and the session that owns it. Typical culprits: an application connection returned to the pool with an uncommitted transaction, an SSMS window where someone ran BEGIN TRAN and walked away, or a batch job stuck mid-transaction.

If the transaction is idle and clearly abandoned, kill the session. Be aware: KILL triggers a rollback, and the rollback can take as long as the original transaction ran. During rollback the log records stay pinned. Plan for that window before you kill a session that has been writing for six hours.

AVAILABILITY_REPLICA: get the secondary caught up

The primary cannot truncate its log until all secondaries have hardened and redone the log records. Check the send and redo queues per replica (Quick check 7). A growing send queue points at network throughput or a disconnected secondary. A growing redo queue means the secondary cannot apply log fast enough, usually an I/O or CPU bottleneck on the secondary.

One gotcha on readable secondaries: user queries on the secondary can block the redo thread, which stalls truncation on the primary. If you see redo stalled alongside heavy read workload on the secondary, that is the mechanism. Resuming suspended data movement, fixing secondary storage latency, or relieving the read workload all restore truncation. Switching the AG to asynchronous commit relieves the primary but accepts data-loss risk on failover; treat it as an emergency lever, not a fix.

REPLICATION: restart or fix the Log Reader Agent

With transactional replication or CDC, the log cannot be truncated until the Log Reader Agent has harvested the transactions. Check whether the agent is running and whether it is erroring against the distribution database. DBCC OPENTRAN will report the oldest non-distributed transaction. Restarting a stopped Log Reader Agent usually resolves it, but if replication is falling behind chronically, size the distribution path properly or the log will fill again on the next heavy write burst.

Disk full: add an emergency log file on another volume (last resort)

If the truncation blocker is fixed but the volume is at zero free, or if you need immediate breathing room while working the root cause, add a second log file on a different volume with free space:

-- EMERGENCY relief only. Adds a log file on a different volume.
ALTER DATABASE YourDatabase
ADD LOG FILE (
    NAME = YourDatabase_log_emergency,
    FILENAME = 'E:\EmergencyLog\YourDatabase_log_emergency.ldf',  -- volume with free space
    SIZE = 512MB,
    FILEGROWTH = 512MB
);

This is a temporary bridge. Multiple log files do not stripe or load-balance; SQL Server fills one before using the next. Once the truncation root cause is fixed and the log has been backed up, remove the extra file and consolidate. Never place log files on compressed file systems, and note that log file growth zero-initializes the new space (Instant File Initialization does not apply to log files), so growth events stall log writes while they run.

TempDB log full: add space, find the long transaction

TempDB uses the simple recovery model, so there is no log backup to take and none will help. The error message may still suggest backing up the log, which is a red herring for TempDB. The fixes are: add space to the TempDB log file or add another TempDB log file on a volume with free space, and find the transaction holding TempDB log space open. A single long-running transaction doing heavy version-store or spill work can pin the TempDB log. Restarting the instance recreates TempDB and clears the condition, but that is an outage of its own; treat it as the final option.

What not to do

• Do not just add disk space. If log_reuse_wait_desc is not NOTHING, the log will eat whatever space you add. You have bought time, nothing more.
• Do not shrink the log as a fix. Shrinking does not address why the log filled, and the log will regrow into the same condition, adding VLF fragmentation each cycle. Shrink only after the root cause is resolved and only when you genuinely need to reclaim permanent over-allocation.
• Do not detach or restart the database hoping it clears. The log will still be full afterward, and if the log fills during recovery after a restart the database can come up in RECOVERY_PENDING.

Prevention

• Monitor log_reuse_wait_desc continuously. Any value other than NOTHING persisting across samples is a ticket before it becomes a page. This single check prevents most 9002 incidents.
• Alert on Percent Log Used above 70% with a non-NOTHING reuse reason, and page above 90%. The degradation curve is a cliff; your only warning is the trend.
• Alert on log backup age, not just job success. Jobs can report success while writing to a dead target. Query msdb.dbo.backupset and alert if the newest type ‘L’ backup for a full recovery database is older than your schedule allows (15-60 minutes is typical).
• Pre-size log files for peak volume between backups and avoid relying on auto-growth during business hours. Alert on every auto-growth event; growth pauses log writes while the new space is zero-initialized.
• Track VLF count per database (via sys.dm_db_log_info on SQL 2016 SP2+, or DBCC LOGINFO on older versions). Above roughly 1000 VLFs, plan a controlled shrink-and-regrow in proper increments.
• Detect long-running transactions proactively. An open transaction blocks truncation and holds locks at the same time. Alert on transactions older than your longest legitimate batch.
• Watch AG send/redo queues if you run availability groups, including redo blocking from readable-secondary workloads.

How Netdata helps

• Netdata collects Percent Log Used per database and trends it over time, so you see the climb toward the cliff hours before error 9002 fires instead of learning about it from application errors.
• Correlating log usage with log_reuse_wait_desc changes, log backup age, and disk free on the log volume lets you distinguish “backup job died” from “volume full” from “open transaction” in one view, without logging into the server during the incident.
• Auto-growth events and VLF count trended over time expose the slow-burn configuration problems (undersized logs, tiny growth increments) that eventually produce the 3 a.m. page.
• For AG environments, send queue and redo queue metrics alongside primary log usage show exactly which secondary is pinning the log and whether the constraint is send (network) or redo (secondary storage).
• Anomaly detection on log growth rate catches the runaway index rebuild or bulk load that outpaces an otherwise healthy log backup schedule.

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 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 worker thread exhaustion: when the instance stops accepting work
• 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 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