On this page
Why it happens
Every change in SQL Server is written to the transaction log before it is hardened to the data file. Error 9002 means that log has run out of usable space: the file cannot grow any further and the existing space cannot be reused. Until space is freed, writes are blocked and the statement is rolled back.
The reason in quotes is the most important part of the message. due to 'LOG_BACKUP' tells you exactly why the log cannot truncate. Common values are LOG_BACKUP (a log backup is needed to free space), ACTIVE_TRANSACTION (a long-running open transaction is pinning the log), AVAILABILITY_REPLICA (a secondary replica has not hardened the log), and REPLICATION (log records are waiting to be read by the replication reader).
The usual causes are a database in the FULL recovery model with no log backups running so the log grows forever, one long transaction that stays open and pins every log record after it, a disk that has filled up, or autogrowth on the log file that is disabled or capped at a maximum size.
Examples
A write fails because the log is full
UPDATE dbo.Orders
SET status = 'shipped'
WHERE status = 'pending';Msg 9002, Level 17, State 2, Line 1 The transaction log for database 'AppDb' is full due to 'LOG_BACKUP'.
Check the reason the log cannot truncate
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = 'AppDb';name recovery_model_desc log_reuse_wait_desc AppDb FULL LOG_BACKUP
A long-running transaction pinning the log
-- The reason reported is ACTIVE_TRANSACTION.
-- Find the oldest open transaction in the current database.
DBCC OPENTRAN;Msg 9002, Level 17, State 2, Line 1 The transaction log for database 'AppDb' is full due to 'ACTIVE_TRANSACTION'.
How to fix it
Back up the transaction log so its space can be reused
When the reason is LOG_BACKUP and the database is in FULL recovery, take a log backup. Once the log is backed up its inactive portion is marked reusable and the file truncates automatically. Do NOT just shrink the file: shrinking does not fix the cause and the log will simply fill again.
BACKUP LOG AppDb
TO DISK = 'D:\Backups\AppDb_log.trn';Find and end the long-running transaction
When the reason is ACTIVE_TRANSACTION, an open transaction is holding the log. Identify it, then commit, roll back, or kill the offending session so the log records behind it can be released.
-- Oldest open transaction in this database
DBCC OPENTRAN;
-- All active transactions on the instance
SELECT session_id, transaction_id, transaction_begin_time
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_tran_session_transactions s
ON t.transaction_id = s.transaction_id;Add disk space or enable autogrowth on the log file
If the disk is full or autogrowth is capped, the file cannot grow. Free space on the volume, or raise the maximum size and make sure autogrowth is enabled with a sensible fixed increment.
ALTER DATABASE AppDb
MODIFY FILE (
NAME = AppDb_log,
MAXSIZE = 20GB,
FILEGROWTH = 512MB
);Switch to SIMPLE recovery if point-in-time recovery is not needed
If you do not need to restore to a point in time between full backups, the SIMPLE recovery model lets the log truncate automatically on every checkpoint, so it never grows unbounded from missing log backups. Understand the trade-off first: you lose the ability to recover past the last full or differential backup.
ALTER DATABASE AppDb SET RECOVERY SIMPLE;How to prevent it
If the database stays in FULL recovery, schedule regular transaction log backups (for example every 15 minutes) so the log is truncated on a steady cadence and never grows without bound. This is the single most effective way to avoid 9002.
Monitor log_reuse_wait_desc in sys.databases and keep an eye on free space on the log volume so you are warned before the file fills. Keep transactions short and commit promptly so no single transaction pins the log, and size the log file for your real workload instead of relying on many small autogrowth events.
Common questions
Why is shrinking the log file not the fix for error 9002?
Shrinking only releases space that is already free, and when the log is full there is no free space to release. It does nothing about the reason the log cannot truncate, so the log fills right back up. The real fix is to remove the cause: back up the log in FULL recovery, end a long-running transaction, add disk space, or switch to SIMPLE recovery.
What is the difference between FULL and SIMPLE recovery model here?
In FULL recovery the log keeps every change until you back it up, which supports point-in-time restore but means the log grows until a log backup truncates it. In SIMPLE recovery the log truncates automatically at each checkpoint, so it does not grow from missing backups, but you can only restore to your last full or differential backup. Choose FULL when you need point-in-time recovery and take regular log backups; choose SIMPLE when you do not.
How do I find out why the log cannot truncate?
Query sys.databases and read the log_reuse_wait_desc column for that database. Values like LOG_BACKUP, ACTIVE_TRANSACTION, AVAILABILITY_REPLICA, and REPLICATION each point to a specific cause, which tells you which fix to apply.