SQL Server Error 9002 Transaction Log Full: Find the Cause and Fix It

SQL Server raises error 9002 ("The transaction log for database '...' is full due to '...'") when the log has no reusable space and can't grow, and the reason in quotes is the same value you'll find in log_reuse_wait_desc in sys.databases. Fix the cause that value names, most often by taking a transaction log backup (LOG_BACKUP), ending a long open transaction (ACTIVE_TRANSACTION) or clearing an availability group secondary's backlog (AVAILABILITY_REPLICA), and if the disk or MAXSIZE is the limit, grow the log or add a temporary log file on another volume. Shrinking the file doesn't help until the log has been truncated.

Who this is for and what you will have at the end

This guide is for DBAs and on-call engineers running SQL Server 2017 or later on Windows or Linux, including databases in Always On availability groups. Azure SQL Database and Azure SQL Managed Instance use the same engine, but their troubleshooting tools differ; Microsoft has separate articles for them, linked in the references.

At the end you will have:

• The reason the log can't be truncated, read from the engine rather than guessed.
• A fix for each common reason, including availability group replicas.
• Steps to create space safely when the disk or file limit is the problem.
• Log sizing and backup settings that stop the error coming back.

What error 9002 does to the database

If the log fills while the database is online, the database stays online but can only be read, not updated. If it fills during recovery, the Database Engine marks the database RESOURCE PENDING. Either way, someone has to act to make log space available. After you fix a database that was in recovery, bring it online with ALTER DATABASE [Sales] SET ONLINE;.

Two terms are easy to confuse:

• Truncation is logical. It marks inactive virtual log files (VLFs) as reusable. Under the simple recovery model it happens after a checkpoint; under full or bulk-logged it happens after a log backup (not a copy-only one), provided a checkpoint has occurred since the previous backup.
• Shrinking is physical. It removes inactive VLFs from the end of the file and returns space to the operating system. It can't free anything until truncation has happened.

One more rule from the Microsoft documentation: never delete or move the transaction log file unless you fully understand the consequences.

Prerequisites

• Membership in sysadmin, or enough permissions on the affected database to query sys.databases, run BACKUP LOG and ALTER DATABASE.
• sysadmin or db_owner if you need DBCC SHRINKFILE.
• A backup destination with free space for log backups.
• For availability groups, access to the primary replica and to the secondaries' servers.

Step 1: Find what is blocking truncation

Run this on the instance. It shows the recovery model and the truncation blocker for every database that has one:

SELECT name,
       recovery_model_desc,
       log_reuse_wait,
       log_reuse_wait_desc
FROM sys.databases
WHERE log_reuse_wait > 0
ORDER BY name;

Then, in the affected database, check how full the log is and how much has been written since the last log backup:

USE [Sales];
SELECT total_log_size_in_bytes / 1048576.0 AS total_log_mb,
       used_log_space_in_bytes / 1048576.0 AS used_log_mb,
       used_log_space_in_percent,
       log_space_in_bytes_since_last_backup / 1048576.0 AS mb_since_last_log_backup
FROM sys.dm_db_log_space_usage;

And check the file limits, because 9002 also appears when autogrow is off or MAXSIZE is reached:

SELECT DB_NAME(database_id) AS database_name,
       name AS logical_name,
       physical_name,
       CONVERT(BIGINT, size) * 8 / 1024 AS size_mb,
       CASE max_size WHEN -1 THEN NULL ELSE CONVERT(BIGINT, max_size) * 8 / 1024 END AS max_size_mb,
       CASE WHEN growth = 0 THEN 'AUTOGROW_DISABLED' ELSE 'Autogrow_Enabled' END AS autogrow
FROM sys.master_files
WHERE type_desc = 'LOG'
  AND database_id = DB_ID(N'Sales');

The log_reuse_wait_desc values you will see:

Microsoft's full transaction log troubleshooting article includes a longer script that runs these checks for every database and prints a recommendation per cause.

Step 2: Fix the cause that log_reuse_wait_desc names

LOG_BACKUP

The database is in the full or bulk-logged recovery model and the log isn't being backed up, or not often enough. Take a log backup:

BACKUP LOG [Sales]
    TO DISK = N'E:\Backup\Sales_LOG_20261011_0915.trn';

If the log has never been backed up, you must take two log backups before the Database Engine can truncate up to the last one. Then find out why backups stopped. This query lists the last two months of backups for full and bulk-logged databases from msdb:

SELECT bs.database_name,
       bs.type,
       bs.is_copy_only,
       bs.backup_start_date,
       bs.backup_finish_date,
       bf.physical_device_name
FROM msdb.dbo.backupset AS bs
LEFT OUTER JOIN msdb.dbo.backupmediafamily AS bf
    ON bs.media_set_id = bf.media_set_id
WHERE bs.recovery_model IN ('FULL', 'BULK-LOGGED')
  AND bs.backup_start_date > DATEADD(MONTH, -2, SYSDATETIME())
ORDER BY bs.database_name, bs.backup_start_date DESC;

Type L is a log backup and D a full database backup. If no L rows exist for a database in full recovery, that is your cause. Schedule regular log backups for every database in full or bulk-logged recovery.

If the database doesn't need point-in-time restore, the simple recovery model may be the right choice. Make it a deliberate decision: back up the log before switching from full, and know that the switch breaks the log backup chain. If you later switch back to full, take a full or differential backup immediately (the full model only takes effect after the first data backup) and schedule log backups again.

ALTER DATABASE [Sales] SET RECOVERY SIMPLE;

ACTIVE_TRANSACTION

A long-running transaction keeps everything after its first log record active, under every recovery model. Find it:

USE [Sales];
DBCC OPENTRAN;
 
SELECT dbtran.database_transaction_begin_time,
       dbtran.database_transaction_state,
       dbtran.database_transaction_log_record_count,
       dbtran.database_transaction_log_bytes_used,
       dbtran.database_transaction_begin_lsn,
       stran.session_id
FROM sys.dm_tran_database_transactions AS dbtran
LEFT OUTER JOIN sys.dm_tran_session_transactions AS stran
    ON dbtran.transaction_id = stran.transaction_id
WHERE dbtran.database_id = DB_ID()
ORDER BY dbtran.database_transaction_begin_time;

DBCC OPENTRAN reports the oldest active transaction and its owner, so you can track down the source and have it committed rather than rolled back. If you must end it, use KILL <session_id> with extreme caution, especially when critical processes are running. In full recovery, you may also need another log backup after the transaction ends before space is freed. Long transactions are also a frequent cause of blocking and deadlocks; see capturing and fixing SQL Server error 1205 deadlocks.

ACTIVE_BACKUP_OR_RESTORE

A data backup or restore is running. Wait for it, or cancel it if it is preventing truncation and the immediate problem matters more:

SELECT session_id, command, percent_complete, start_time
FROM sys.dm_exec_requests
WHERE command LIKE 'BACKUP%' OR command LIKE 'RESTORE%';

AVAILABILITY_REPLICA

The primary can't reuse log blocks until every secondary has received and hardened them, and until every secondary's redo thread has applied them. This happens in both synchronous and asynchronous commit mode. The two scenarios are latency delivering the log to a secondary (network or disk) and redo latency on a secondary.

Run this on the primary to see which secondary holds the oldest truncation_lsn, which is the limit on what the primary can reclaim:

SELECT ag.name AS availability_group_name,
       d.name AS database_name,
       ar.replica_server_name,
       drs.truncation_lsn,
       drs.log_send_queue_size,
       drs.redo_queue_size
FROM sys.availability_groups AS ag
INNER JOIN sys.availability_replicas AS ar
    ON ar.group_id = ag.group_id
INNER JOIN sys.dm_hadr_database_replica_states AS drs
    ON drs.replica_id = ar.replica_id
INNER JOIN sys.databases AS d
    ON d.database_id = drs.database_id
WHERE drs.is_local = 0
ORDER BY ag.name, d.name, drs.truncation_lsn, ar.replica_server_name;

A large log_send_queue_size points to delivery (network throughput or latency). A large redo_queue_size points to the secondary's redo thread, which is often blocked by read workloads on a readable secondary; the lock_redo_blocked extended event shows when and on what objects. Corrective actions, starting with the least disruptive:

• Remove the resource or performance bottleneck on the secondary (CPU, disk, network).
• If redo is frequently blocked, set ALLOW_CONNECTIONS for that replica's SECONDARY_ROLE to NO until the redo queue drains, then re-enable reads.
• Enable autogrow, raise MAXSIZE or add a log file (Step 3) to buy time.
• As a last resort, remove the database from the availability group on the offending secondary. That removes high availability and disaster recovery for that copy, and you may have to set the availability group up again later.

REPLICATION, change tracking or CDC

Transactional replication, change tracking and change data capture read the log. If the Log Reader Agent or capture job isn't running or is failing, the log can't truncate. Check the oldest non-distributed transaction with DBCC OPENTRAN, check the agents in Replication Monitor, and restart or fix the failing agent or job.

CHECKPOINT, OLDEST_PAGE and XTP_CHECKPOINT

These are usually short-lived. If one persists, run a manual checkpoint and look at the VLFs:

USE [Sales];
CHECKPOINT;
SELECT * FROM sys.dm_db_log_info(DB_ID(N'Sales'));

For a persistent OLDEST_PAGE, the troubleshooting script suggests temporarily disabling indirect checkpoints with ALTER DATABASE [Sales] SET TARGET_RECOVERY_TIME = 0 SECONDS;. Restore your normal value afterwards. For XTP_CHECKPOINT, memory-optimized tables take an automatic checkpoint once the log has grown by 1.5 GB since the last one; a manual CHECKPOINT forces it.

Step 3: Create space when the disk or file limit is the problem

If the volume holding the log is full, or the file has hit MAXSIZE, you need room while you fix the cause:

1. Free space on the volume by moving or deleting other files, so autogrow can extend the log.
2. Grow the existing file or lift its limit. A single log file can be at most 2 TB.

ALTER DATABASE [Sales]
    MODIFY FILE (NAME = N'Sales_log', SIZE = 64GB, MAXSIZE = 256GB, FILEGROWTH = 1024MB);

3. If neither is possible, add a second log file on a volume with free space. Treat it as temporary: most databases should have one log file, and multiple log files don't improve performance because the log isn't written with proportional fill.

ALTER DATABASE [Sales]
    ADD LOG FILE (NAME = N'Sales_log_temp', FILENAME = N'F:\SQLLog\Sales_log_temp.ldf', SIZE = 8GB, FILEGROWTH = 1024MB);

Never place log files on compressed file systems. If you need to move the log permanently, follow Microsoft's procedure for moving database files rather than copying the file while the database is in use.

Step 4: Shrink only if the file grew far beyond normal

After truncation, check used_log_space_in_percent again. If the file grew to an abnormal size because of a one-off event, shrink it back to its normal working size. target_size is in MB and, for log files, means the free space left after the shrink:

USE [Sales];
DBCC SHRINKFILE (N'Sales_log', 16384);

A log file can only shrink to a VLF boundary, and only while at least one VLF is free. If it doesn't shrink, the log hasn't been truncated; take another log backup and try again. Don't shrink as regular maintenance: if normal operations need the space, the file grows again and you pay the growth cost every time. If you added a temporary log file in Step 3, plan its removal once the original file has room again.

Step 5: Stop it happening again

• Take log backups often enough for the write rate of each full or bulk-logged database. The mb_since_last_log_backup value from Step 1 tells you how fast the log fills between backups.
• Size the log for the busiest window, not the average: the time a full backup runs (log backups can't occur until it finishes), your largest index maintenance operation, and your largest batch.
• Set FILEGROWTH as a fixed size, not a percentage, and don't set it above 1,024 MB for log files. Since SQL Server 2016 the default growth for log files is 64 MB. From SQL Server 2022, log growth events up to 64 MB can use instant file initialization; larger increments can't, and in earlier versions log files never use it.
• Avoid very small increments (too many VLFs) and very large ones (the database can pause while the new space is allocated, and you get too few, large VLFs).
• Keep AUTO_SHRINK off, which is the default.
• Alert on used_log_space_in_percent and on log_reuse_wait_desc values other than NOTHING, CHECKPOINT or LOG_BACKUP lasting longer than your normal backup interval.
• For availability groups, monitor log_send_queue_size and redo_queue_size on every secondary.

Verification

1. log_reuse_wait_desc for the database returns NOTHING (or LOG_BACKUP between scheduled backups, which is normal).
2. used_log_space_in_percent drops after the next log backup and stays well below 100 percent through your busiest window.
3. A test update in the database succeeds, confirming it is no longer effectively read-only.
4. The msdb backup history shows log backups at the expected interval.
5. For availability groups, truncation_lsn advances on every secondary and the queues shrink.

Troubleshooting

Checklist

• Read log_reuse_wait_desc before changing anything.
• Check log space with sys.dm_db_log_space_usage and file limits with sys.master_files.
• Fix the named cause: log backup, open transaction, backup in progress, AG lag, replication or checkpoint.
• Create space only as needed: free disk, grow the file, or add a temporary log file.
• Shrink only after truncation, and only back to normal working size.
• Fix the backup schedule and growth settings so it doesn't recur.
• Remove any temporary log file once the incident is over.

References

• Troubleshoot a full transaction log (SQL Server error 9002)
• The transaction log
• Manage the size of the transaction log file
• Error 9002 when the transaction log is large (AVAILABILITY_REPLICA)
• View or change the recovery model of a database
• DBCC SHRINKFILE
• Troubleshooting transaction log errors with Azure SQL Database
• Troubleshooting transaction log errors with Azure SQL Managed Instance