SQL Server problems
Error 9002: The Transaction Log Is Full. What Holds It, and the Fix
22 November 2026
Error 9002 stops every change in one database while reads go on. The log is full for one of two reasons: something keeps SQL Server from reusing it, or it has no room left to grow. One column names the reason, and each reason has its own fix.
What the error says
Msg 9002, Level 17, State 2: The transaction log for database 'Sales' is full due to 'LOG_BACKUP'. The word in quotes at the end is the answer SQL Server already has: it is the value of log_reuse_wait_desc for that database.
The transaction log is circular. A part of it can be reused once nothing needs it any more: no open transaction, no log backup that has not been taken, no replica that has not received it. When the head of the log reaches a part that is still needed, the log grows. When it cannot grow, the database returns 9002.
Why the log is not reused
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
ORDER BY name;
| log_reuse_wait_desc | What holds the log |
|---|---|
LOG_BACKUP | The database is in FULL or BULK_LOGGED recovery and the log has not been backed up. |
ACTIVE_TRANSACTION | A transaction is still open, often in a session that sleeps. |
AVAILABILITY_REPLICA | A secondary replica of an availability group has not received or applied the log. |
REPLICATION | The log reader of replication or change data capture has not read it. |
ACTIVE_BACKUP_OR_RESTORE | A backup or a restore is running. |
CHECKPOINT | No checkpoint since the last truncation. It clears by itself. |
NOTHING | The log can be reused. If it is still full, it has simply no room to grow. |
How full it is, and whether it can grow
DBCC SQLPERF(LOGSPACE); lists the size and the used percent of every log. The files, their limits and the free space of their volumes come from one query:
SELECT DB_NAME(mf.database_id) AS database_name,
mf.name AS file_name,
mf.size / 128 AS size_mb,
CASE mf.max_size
WHEN -1 THEN 'unlimited'
WHEN 268435456 THEN '2 TB'
ELSE CAST(mf.max_size / 128 AS varchar(20)) + ' MB'
END AS max_size,
CASE mf.growth WHEN 0 THEN 'fixed' ELSE 'grows' END
AS growth,
vs.available_bytes / 1048576 AS volume_free_mb
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id,
mf.file_id) vs
WHERE mf.type_desc = 'LOG'
ORDER BY database_name;
A log with a fixed size, or one whose volume has no free space, is full at 100%. A log that can still grow is in trouble only when what holds it does not go away.
Which transaction holds it
For ACTIVE_TRANSACTION, the oldest open transaction of the database and its session:
SELECT s.session_id, s.login_name, s.host_name,
s.program_name, s.status,
dt.database_transaction_begin_time AS began,
dt.database_transaction_log_bytes_used / 1048576
AS log_mb
FROM sys.dm_tran_database_transactions dt
JOIN sys.dm_tran_session_transactions st
ON st.transaction_id = dt.transaction_id
JOIN sys.dm_exec_sessions s
ON s.session_id = st.session_id
WHERE dt.database_id = DB_ID('Sales')
AND dt.database_transaction_begin_time IS NOT NULL
ORDER BY dt.database_transaction_begin_time;
A session with status sleeping at the top of this list is an application that opened a transaction and never closed it.
The fix, in order
- LOG_BACKUP: back up the log.
BACKUP LOG [Sales] TO DISK = N'R:\Backup\Sales_log.trn';If the used percent does not fall, take a second one: the first backup can leave the active part at the end of the file. Then find out why the log backup job stopped. - ACTIVE_TRANSACTION: end the transaction. Ask the owner to commit, or
KILLthe session. The rollback writes log too and takes time in proportion to the work done;KILL 57 WITH STATUSONLY;shows its progress. - AVAILABILITY_REPLICA: look at the secondary.
sys.dm_hadr_database_replica_statesshowslog_send_queue_sizeandredo_queue_sizefor each replica. Resume a suspended database, or remove a replica that is gone for good, because the primary keeps log for it for as long as it is a member. - REPLICATION: start the log reader. The log reader agent or the change data capture job is stopped or behind.
- Give the log room while you fix the cause. Raise the limit of the file, or add a second log file on a volume with space:
ALTER DATABASE [Sales] ADD LOG FILE (NAME = Sales_log2, FILENAME = N'L:\Log\Sales_log2.ldf', SIZE = 8GB);Remove it again once the log is healthy. - Do not switch to SIMPLE to make it go away. It breaks the log backup chain and the point-in-time restore with it. If the database truly needs no point-in-time restore, make that a decision, then take a full backup.
See it coming
A log does not fill in one step. It climbs for hours while a log backup job fails quietly or a transaction stays open. Radar Engine, the detection layer of Scops, measures that climb on every database, with no agent on the server:
| Detector | When it fires |
|---|---|
| Transaction log fill | Measured against what the log may reach: its size limit and the free space of its volume. Watch from 80% and rising. Warning when full within 6 hours, or from 90% with a reuse wait that does not clear by itself. Critical when full within 30 minutes, or from 95% of a log that cannot grow. |
| Log backup age | Against the database's own usual interval. Warning at 3 times the interval, Critical at 6 times. |
| AG replica lag | A secondary that falls behind, before the primary's log pays for it. |
The finding names the database, the reason from log_reuse_wait_desc and the time left. A log that fills and empties with every log backup is its routine and stays quiet.
Questions
Does a full backup truncate the transaction log?
No. In FULL and BULK_LOGGED recovery only a log backup lets the log be reused. A database that gets full backups and no log backups fills its log sooner or later.
Why is the log full in SIMPLE recovery?
Most often because of an open transaction. SIMPLE recovery reuses the log at every checkpoint, but never past the oldest open transaction. log_reuse_wait_desc shows ACTIVE_TRANSACTION.
Does shrinking the log file fix error 9002?
No. Shrinking removes free space, and a full log has none. The fix is to remove what holds the log, then the space inside it is reused.
How do I see how full the transaction log is?
DBCC SQLPERF(LOGSPACE) lists every database's log size and used percent. sys.dm_db_log_space_usage gives the same for the current database.
Related problems
- SQL Server blocking: find the head blocker
- tempdb is full: what uses it
- Availability group lag: send and redo queues
- All SQL Server problems
- SQL Server monitoring without agents