Error 9002: The Transaction Log Is Full – Find the log_reuse_wait Reason

Fix it now

The log has no space it is allowed to reuse, which is not the same as being full of live transactions and is almost never fixed by shrinking. SQL Server will tell you what is holding it if you ask: the message itself points at log_reuse_wait_desc in sys.databases, and that one column turns this from guesswork into a two-minute diagnosis.

SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = N'MyDb';
DBCC SQLPERF(LOGSPACE);

1. LOG_BACKUP: take one. BACKUP LOG [MyDb] TO DISK = N'D:\bk\MyDb_log.trn'; then re-check the column. A database whose log has never been backed up may need two before space is released.
2. ACTIVE_TRANSACTION: find it. USE MyDb; DBCC OPENTRAN; then decide whether to wait for it or end it.
3. CHECKPOINT: run one. CHECKPOINT; then examine the virtual log files with SELECT * FROM sys.dm_db_log_info(DB_ID(N'MyDb'));
4. REPLICATION: check the log reader agent or the change data capture job is running and caught up.
5. Only if the volume itself is out of space, add a second log file on another volume as a temporary measure, then remove it once the real cause is cleared.

Shrinking is not a solution to a full log. It only reclaims physical space that is already free inside the file, so the log has to be truncated first and the file will grow straight back if the cause is untouched.

If log_reuse_wait_desc now reads NOTHING and writes succeed, you are done. If it still names something, the next section explains what each value is telling you.

Why it happens

The transaction log is used circularly. Internally it is divided into virtual log files, and each one becomes reusable when every record in it is no longer needed for recovery, replication, backup, or any other consumer. Truncation is the act of marking them reusable; it does not shrink the file and it does not delete anything you can see. When no virtual log file can be marked reusable and the physical file cannot grow, you get 9002.

The distinction between truncation and shrinking is the one that saves the most time. Truncation is a logical operation that frees space inside the file for reuse. Shrinking is a physical operation that returns space to the file system. Shrinking cannot help a full log, because there is no free space inside the file to return, and Microsoft’s own page says so directly. Worse, the data moved during a shrink is scattered wherever there is room, which fragments indexes and slows the queries that scan them.

In simple recovery a checkpoint is enough to release the space. In full or bulk-logged recovery nothing is released until a log backup is taken, and this is by a wide margin the most common cause of 9002: a database left in full recovery because that is the default, with nobody ever backing up its log. The file then grows until the disk stops it.

Every other reason is some consumer holding the records. An open transaction pins everything written since it began, however small it is. Replication and change data capture hold records until the reader has processed them. A running backup or restore, an availability replica that has fallen behind, or a snapshot being created will each hold the log briefly. Every one of those has its own value in log_reuse_wait_desc, which is why reading that column first saves an hour.

Full reference

Reading log_reuse_wait_desc, every documented value

The neighbouring codes and what each adds

5901 is the one worth not skipping. Its published text names database corruption as a possibility alongside resource shortage, and it tells you to read the earlier entries in the error log. A checkpoint failure that keeps recurring on a database with plenty of disk is a reason to run DBCC CHECKDB, not a reason to add another log file.

Shrinking, and when it is legitimate

• Truncation frees space inside the file for reuse. Shrinking returns free space to the file system. They are different operations and only one of them is relevant to a full log.
• Shrinking cannot work unless there is already empty space inside the log file, which means the log has to be truncated first.
• Shrinking happens at virtual log file boundaries, so the result rarely matches the size you asked for.
• Microsoft warns that data moved to shrink a file is scattered to any available location, which fragments indexes and can slow range queries. Consider rebuilding indexes on the file afterwards.
• Shrink once, deliberately, after the underlying cause is fixed and only if the file is genuinely oversized. Never on a schedule.

Sizing the log so this stops happening

Size it for the busiest transaction you run plus the work generated between two log backups, and set it up front rather than reaching it through autogrow. Watch used_log_space_in_percent in sys.dm_db_log_space_usage across a normal week and size for the peak, not for the average. Use a fixed growth increment in megabytes rather than a percentage, because a percentage of a large file is an unpredictable increment that arrives at the worst possible moment.

Switching a database to simple recovery to force the log to clear breaks the log backup chain immediately. Everything since your last full backup becomes unusable for point-in-time recovery. If you do it deliberately, take a full backup straight afterwards and accept the gap.

Every code this article covers

Confirm the fix worked

1. Re-run SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = N'MyDb'; and confirm it reads NOTHING.
2. Run DBCC SQLPERF(LOGSPACE); and confirm the used percentage has dropped.
3. Run a write that previously failed and confirm it completes.
4. Confirm the log backup job is scheduled and succeeding if the database is in full recovery.
5. If you added a second log file, confirm it has been removed once the original log has reusable space.

Questions people ask about this