SQL Server Error 9002: Your Transaction Log Is Full

It's 2am. Your phone wakes you. Rub your eyes, check your email, and there it is:

Error: 9002, Severity: 17, State: 4
The transaction log for database 'trading' is full due to 'LOG_BACKUP'.

The database is still online. Looks ok. You can read from it. But every INSERT, UPDATE, and DELETE fails. Production night-trading is effectively down.

The good news: It's fixable -- but, that fix depends entirely on what's preventing log truncation.

Step 1: Find Out Why

Run this:

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

The log_reuse_wait_desc column tells you exactly what's blocking truncation. Here are some different results you may see and how to respond:

log_reuse_wait_desc = LOG_BACKUP

You're in FULL or BULK_LOGGED recovery and the log hasn't been backed up recently. Check when the last successful log backup ran:

SELECT database_name, backup_finish_date, backup_size
FROM msdb.dbo.backupset
WHERE type = 'L' 
AND database_name = 'trading'
ORDER BY backup_finish_date DESC;

If backups are running but this still happens, either they're failing (check job history) or the frequency isn't keeping up with your transaction volume. Take a log backup now:

BACKUP LOG YourDatabase TO DISK = 'D:\Backups\trading_log.trn';

Then increase backup frequency if needed.

log_reuse_wait_desc = ACTIVE_TRANSACTION

Something's holding a transaction open. Find it:

DBCC OPENTRAN('trading');

You'll get the SPID of the oldest open transaction. Investigate with DBCC INPUTBUFFER(spid) to see what it's running. If it's abandoned, kill it. If it's legitimate, wait or have a conversation with the application team about transaction scope. It could be anything. Recently we had a CDC job that was running long -- log_reuse_wait was CDC.

log_reuse_wait_desc = REPLICATION

The Log Reader Agent isn't keeping up, or there's orphaned replication metadata. The fastest path to check status is the Replication Monitor:

• In SSMS, right-click the Replication folder in Object Explorer and select Launch Replication Monitor.

• Expand the Publisher and publication groups to see the status of all agents and subscriptions. The 'All Subscriptions' tab displays details, including 'Undistributed commands' — that's your backlog.

log_reuse_wait_desc = AVAILABILITY_REPLICA

Your AG secondary isn't keeping up. The primary can't truncate the log until it's been delivered, hardened, and applied on all secondaries. Check the queue on the primary and/or the redo waits on the secondary:

-- run on primary
SELECT 
    db.name AS database_name,
    drs.synchronization_state_desc,
    drs.log_send_queue_size AS log_send_queue_kb,
    drs.redo_queue_size AS redo_queue_kb,
    drs.redo_rate,
    drs.last_sent_time,
    drs.last_redone_time
FROM sys.dm_hadr_database_replica_states drs JOIN sys.databases db 
  ON drs.database_id = db.database_id
WHERE drs.is_local = 0
ORDER BY drs.redo_queue_size DESC;

-- run on secondary to check for redo thread waits
SELECT * FROM sys.dm_exec_requests 
WHERE command LIKE '%REDO%' OR command LIKE '%HADR%'
ORDER BY start_time DESC;

High log_send_queue_size means network latency or throughput issues. High redo_queue_size means the secondary can't apply changes fast enough — check for resource bottlenecks or blocking on that replica. If a secondary is way behind, you have a performance problem on that replica.

NOTHING

If it says NOTHING, the log should truncate on the next checkpoint or log backup. If you're still out of space, you may just need to shrink:

DBCC SHRINKFILE(trading_log, 1024);

Yes, I know shrinking is generally bad. At 2AM with a full log, you may feel differently.

Prevention

If you need point-in-time recovery, then you need to be FULL Recovery Model and you need transaction log backups. Period. Every 15-30 minutes often works well for busy databases, but the frequency is up to you. If you don't need point-in-time recovery, make things easy. Switch to SIMPLE Recovery Model. Problem solved.

More to Read

• Microsoft: Troubleshoot a Full Transaction Log (SQL Server Error 9002)
• Microsoft: The Transaction Log