How to fix SQL Server error 9002 (the transaction log is full)

This means the transaction log for a database has run out of room, so no new transactions can commit until space is freed. The fix is to find what is stopping the log from clearing, deal with that, then back up or grow the log. Jump to your situation below or work through the methods in order.

By Neeraj Singh ~9 min Updated Jun 2026 95% found this helpful

Error message

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

Summary

Error 9002 means a database transaction log is full, so SQL Server cannot write any more changes and the database becomes read-only until space is freed. The single most useful step is to check log_reuse_wait_desc in sys.databases, which names exactly what is stopping the log from clearing. The most common reason is a FULL recovery database whose log has never been backed up, in which case a log backup releases the space safely. Other reasons include a long open transaction, replication or Change Data Capture, an Availability Group secondary that has fallen behind, or a log file that has hit its size cap with autogrow disabled. Fix the named blocker first, then grow the log or free disk space, and only shrink afterwards if you really need to. Set up regular log backups so it does not happen again.

What this error means

Error 9002 (Severity 17) is raised when the transaction log for a database fills up. SQL Server writes every change to the log first, so when the log has no reusable space and cannot grow, it stops accepting writes. The database stays online and readable, but inserts, updates and deletes fail until you free space.

Space in the log is normally reused once the records in it are no longer needed. When something holds those records open, the log keeps growing until it is full. The log_reuse_wait_desc column in sys.databases tells you precisely what that something is, which is why every fix starts there rather than blindly shrinking the file.

Common causes

The database is in FULL or BULK_LOGGED recovery and the log has never been backed up (LOG_BACKUP).

A long-running or uncommitted transaction is holding the log open (ACTIVE_TRANSACTION).

Transactional replication or Change Data Capture has not read the log yet (REPLICATION).

An Availability Group secondary is behind on hardening or redo (AVAILABILITY_REPLICA).

The log file has reached its maximum size, or autogrow is disabled (growth set to 0).

The physical disk holding the LDF has run out of free space.

An active backup or restore is in progress and holding the log (ACTIVE_BACKUP_OR_RESTORE).

Expert insight

“Nine times out of ten, 9002 is not a disk problem, it is a backup problem. People panic and start shrinking the log, which is exactly the wrong first move. I always run one query against sys.databases and read log_reuse_wait_desc, because it tells me the real blocker in one word. If it says LOG_BACKUP, a transaction log backup fixes it in seconds and loses nothing. Only after the blocker is cleared do I even think about growing or shrinking the file, and then I set up scheduled log backups so the same fire does not start next week.”

Neeraj Singh
Manager, Tech Support & Operations · 19+ years fixing Windows and system errors

✓ How to fix it

Method 1

Find why the log will not clear

1Start here every time. Run this to see the recovery model and the exact reason space is not being reused:

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

2Read the log_reuse_wait_desc value: LOG_BACKUP, ACTIVE_TRANSACTION, REPLICATION, AVAILABILITY_REPLICA and so on. It names the blocker.

3Match that value to the matching method below, rather than jumping straight to a shrink.

Method 2

Back up the transaction log (FULL or BULK_LOGGED)

1If the wait is LOG_BACKUP and the database is in FULL or BULK_LOGGED recovery, a log backup releases the inactive space safely:

BACKUP LOG [YourDB] TO DISK = 'D:\Backup\YourDB_log.trn';

2If the log has never been backed up, take two log backups so the engine can truncate up to the last backup.

3Re-run the diagnostic query. When the wait clears to NOTHING, the space is reusable again.

Method 3

Find and end a long-running transaction

1If the wait is ACTIVE_TRANSACTION, an open transaction is pinning the log. Find the oldest one:

DBCC OPENTRAN;
SELECT session_id, status, command, DB_NAME(database_id) AS db, start_time
FROM sys.dm_exec_requests
WHERE database_id = DB_ID('YourDB');

2Where it is safe, commit the transaction rather than rolling it back, because a rollback also has to be logged.

3If a session is abandoned, KILL it only when you understand what work will be undone.

Method 4

Grow the log or enable autogrow

1If the log has hit its cap or autogrow is off (growth = 0), give it room:

ALTER DATABASE [YourDB]
MODIFY FILE (NAME = YourDB_log, SIZE = 4096MB, FILEGROWTH = 512MB, MAXSIZE = UNLIMITED);

2Use your own logical log-file name, which you can read from sys.master_files.

3This buys space but does not address the blocker, so still clear the wait reason from the other methods.

Method 5

Free disk space or move the log

1If the LDF drive itself is full, remove unneeded files to make room, or extend the volume.

2If the drive cannot grow, move the log to a larger disk by taking the database offline, copying the LDF, and pointing to the new path with ALTER DATABASE ... MODIFY FILE.

3Keep the log on its own disk where possible so one full volume cannot stall the database.

Method 6

Clear a replication, CDC or Availability Group blocker

1If the wait is REPLICATION, confirm what is configured:

SELECT is_published, is_subscribed, is_cdc_enabled
FROM sys.databases WHERE name = 'YourDB';

2Get the Log Reader Agent running again, or for an orphaned setup remove replication with sp_removedbreplication after confirming it is truly unused.

3If the wait is AVAILABILITY_REPLICA, find and fix the secondary whose redo has fallen behind, since the primary cannot truncate past it.

Method 7

After the blocker clears, checkpoint then shrink if needed

1Only once the wait is NOTHING is the space truly reusable. Flush pending writes and shrink if the file is oversized:

CHECKPOINT;
DBCC SHRINKFILE (YourDB_log, 1024);

2Shrink to a sensible size, not to nothing, and check the VLF count afterwards.

3Do not make shrinking a routine job, repeated shrink and grow fragments the log and slows recovery.

Method 8

Prevent it from happening again

1Schedule regular transaction log backups on every FULL or BULK_LOGGED database, which is the real cure for the common LOG_BACKUP case.

2Right-size the log once so it rarely needs to autogrow, and leave autogrow enabled as a safety net.

3Monitor free disk space and log_reuse_wait_desc so you catch a stuck log before it fills.

If you do not need point-in-time recovery for a database, switching it to SIMPLE recovery stops the LOG_BACKUP problem, but it breaks the log backup chain and you lose the ability to restore to a moment in time. The dev-only reset of SET RECOVERY SIMPLE, shrink, then SET RECOVERY FULL should never be run on a production database without taking a fresh full backup straight after.

Frequently asked questions

What does SQL Server error 9002 mean?

It means a database transaction log is full, so SQL Server cannot record new changes. The database stays readable but inserts, updates and deletes fail until you free space in the log.

How do I find why the log will not clear?

Run SELECT name, log_reuse_wait_desc, recovery_model_desc FROM sys.databases. The log_reuse_wait_desc column names the blocker, such as LOG_BACKUP, ACTIVE_TRANSACTION or REPLICATION.

Should I shrink the log to fix 9002?

Not first. Shrinking before the blocker clears does nothing and fragments the file. Clear the wait reason, then shrink only if the log is genuinely oversized.

It says LOG_BACKUP but I do not take log backups, what do I do?

Either start taking regular log backups, or if you do not need point-in-time recovery switch the database to SIMPLE recovery. SIMPLE means you can no longer restore the log, so back up fully first.

Will fixing 9002 lose data?

No. Backing up the log and freeing space loses nothing. Ending an open transaction rolls back only that unfinished transaction, which had not committed anyway.

How do I stop error 9002 happening again?

Schedule regular log backups, right-size the log so it rarely autogrows, leave autogrow enabled, and monitor disk space and log_reuse_wait_desc.

Still not working?

If the wait reason keeps coming back, or the log refuses to truncate even after a backup, something is still holding it open such as an orphaned replication setup or a stuck Availability Group secondary. Re-check log_reuse_wait_desc after each change, and on a production server bring in your DBA before forcing a recovery model change. You can also submit your error to us for a tailored fix.

Was this fix helpful? Thanks for your feedback!