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

CauseWhat it looks likeFirst thing to check
Missing log backups (LOG_BACKUP)Full recovery model database, log backup job failed, disabled, or never existedmsdb.dbo.backupset for last type ‘L’ backup; backup job status
Long-running open transaction (ACTIVE_TRANSACTION)One session holding a transaction open for hours, often an idle connection from a pool leak or an SSMS window someone left openDBCC OPENTRAN in the database context
AG secondary behind (AVAILABILITY_REPLICA)Primary cannot truncate because a secondary has not hardened the log; secondary disconnected or redo blockedSend queue and redo queue in sys.dm_hadr_database_replica_states
Replication not consuming the log (REPLICATION)Log Reader Agent stopped or failing; transactions not delivered to the distributorReplication agent status, DBCC OPENTRAN for oldest non-distributed transaction
Runaway log generationLog backups are running fine but a huge index rebuild or bulk load is generating log faster than it can be truncated and backed upActive batch operations, recent maintenance jobs
Volume fullLog has headroom configured but the underlying disk is at zero freesys.dm_os_volume_stats for the log volume
TempDB log fullError 9002 on tempdb; writes fail across every database on the instanceTempDB file sizes and free space; active transactions in TempDB

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

SignalWhy it mattersWarning sign
Percent Log Used per databaseDirect measure of how close you are to the cliffAbove 70% with a non-NOTHING reuse reason; above 90% is imminent write failure
log_reuse_wait_descTells you truncation is broken before the log fillsAnything other than NOTHING persisting across samples
Log backup age (msdb.dbo.backupset, type ‘L’)The number one root cause is a silently broken backup jobNo log backup in over an hour on a full recovery database
Oldest open transaction durationOpen transactions block truncation and hold locks simultaneouslyTransaction open for longer than your longest legitimate batch
AG send queue and redo queueAG lag on a secondary blocks truncation on the primarySustained queue growth; redo queue catch-up time exceeding failover RTO
Log file auto-growth eventsGrowth pauses all log-writing activity; Instant File Initialization does not apply to log filesAny auto-growth during business hours
Free space on log volumesLog growth fails at zero free even with maxsize headroomBelow 20% free, or free bytes less than the next growth increment
VLF count per databaseThousands of small auto-growths create VLF fragmentation that slows recovery and backupsAbove 1000 VLFs; above 10,000 recovery will be noticeably slow

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 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.