SQL Server "Transaction log is full": Pinpointing the Exact Cause

Decoding log_reuse_wait_desc in Error 9002, tailored resolutions for LOG_BACKUP, ACTIVE_TRANSACTION, and AVAILABILITY_REPLICA, plus VLF tuning.

Reviewed by:Senior Database Architect & SRE (dataiotech Labs)

Tested Versions: SQL Server 2017 / 2019 / 2022

First 60 Seconds Emergency Protocol:

The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.

▸Symptoms

plaintext

The transaction log for database 'uygulama_db' is full due to 'LOG_BACKUP'.

The phrase inside single quotes is the diagnosis itself. Error 9002 is triggered by seven distinct conditions.

▸Diagnostic Query

sql

SELECT name, log_reuse_wait_desc, recovery_model_desc
FROM sys.databases
WHERE name = 'uygulama_db';

▸Resolution by Cause

1LOG_BACKUP: Database is in FULL recovery model without log backups. Execute BACKUP LOG [db] TO DISK = '...' WITH COMPRESSION; and schedule jobs every 5–15 minutes.
2ACTIVE_TRANSACTION: Locate uncommitted transactions via sys.dm_tran_active_transactions and terminate or commit them.
3AVAILABILITY_REPLICA: Check Always On log_send_queue_size via sys.dm_hadr_database_replica_states.
4VLF Optimization: Avoid excessive auto-growths resulting in thousands of Virtual Log Files.

Other Database Error Triage Playbooks

Other battle-tested diagnostic guides for high-severity database errors.

All Errors (20)

SQLSERVER5 min

SQL Server Error 18456: Root Cause by State Code Matrix

In Error 18456, the State code reveals the actual root cause. Specific fixes for State 5, 8, 11, 18, 38, and 40, with ERRORLOG extraction scripts.

SQLSERVER5 min

SQL Server Error 1205 Deadlock: Graph Extraction and Prevention

Extracting the deadlock graph from the system_health Extended Events ring buffer, understanding victim election, and architectural resolution.

SQLSERVER5 min

SQL Server Pool Timeout: Connection Pool Exhaustion Fix

Difference between connection pool timeout and query command timeout, detecting connection leaks, and .NET DbContext architectural fixes.