SQL Server error 9002: Transaction log for database is full
The database's log file is full and can't grow, so writes fail. The message's reason (LOG_BACKUP, ACTIVE_TRANSACTION...) says what keeps the log from being reused.
- Error number
9002- Severity
- 17
- Category
- Memory, log and storage
What it means
SQL Server writes every change to the transaction log before it changes data. Log space is reused only once nothing still needs it: in the full recovery model that means after a log backup, and in every model the oldest open transaction, replication or an availability group replica can hold it. If the log can't be reused and can't grow (it hit its maximum size or the disk is full), every write fails with 9002.
The message names the reason, which is the same value as log_reuse_wait_desc in sys.databases.
Common causes
LOG_BACKUP: the database is in the full or bulk-logged recovery model and log backups aren't being taken.ACTIVE_TRANSACTION: a long-running or forgotten open transaction pins the log.REPLICATIONorAVAILABILITY_REPLICA: a log reader or a secondary replica has fallen behind.- The log file has a fixed maximum size, or the disk holding it is full.
- A single huge transaction, such as deleting millions of rows in one statement.
How to fix it
-
See why the log can't be reused.
SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = N'SalesDb';
-
For
LOG_BACKUP, take a log backup (and schedule them).BACKUP LOG SalesDb TO DISK = N'D:\Backup\SalesDb_log.trn';
-
For
ACTIVE_TRANSACTION, find the oldest open transaction and get it committed or rolled back.DBCC OPENTRAN (N'SalesDb');
-
Give the log room: let it grow, or add a second log file on another disk.
ALTER DATABASE SalesDb MODIFY FILE (NAME = SalesDb_log, MAXSIZE = UNLIMITED, FILEGROWTH = 512MB);
-
Break huge data changes into batches so each transaction is small.
WHILE 1 = 1 BEGIN DELETE TOP (10000) FROM dbo.EventLog WHERE CreatedAt < '2024-01-01'; IF @@ROWCOUNT = 0 BREAK; END; -
If you don't need point-in-time restore for this database, the simple recovery model reuses log space after each checkpoint.
ALTER DATABASE SalesDb SET RECOVERY SIMPLE;
Questions
Should I shrink the log file after fixing 9002?
Only if it grew far beyond its normal size because of a one-off event. Shrinking a log that regularly needs that space just makes it grow again, which costs time while writes wait.
Related errors
1105Filegroup is full, cannot allocate1101Could not allocate a new page9001The log for database is not available53100PostgreSQL: disk fullORA-00257Oracle: archiver errorORA-30036Oracle: unable to extend segment in undo tablespace
More memory, log and storage errors
701Insufficient memory in resource pool8645Timeout waiting for memory resources8623Query processor ran out of resources1204Cannot obtain a LOCK resource
The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.
Work on SQL Server in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.