Introduction

It’s 2:00 AM. Your monitoring system raises an alert, and the application team reports that users can no longer save transactions.

You connect to SQL Server and discover this error:

Error 9002: The transaction log for database ‘SalesDB’ is full due to ‘LOG_BACKUP’.

The immediate reaction might be to increase the transaction log file size or add another log file.

But hold on.

A full transaction log is a symptom, not necessarily the root cause.

The log might be waiting for a backup, an active transaction might be preventing log reuse, or SQL Server might be unable to reuse space because of another database condition.

Increasing the file size without identifying the cause may only delay the next incident.

In this practical guide, we’ll investigate SQL Server Error 9002 step by step, understand what the different causes mean, choose the correct fix, and validate that the database is healthy again.

1. What Is SQL Server Error 9002?

SQL Server uses the transaction log to record database changes so that transactions can be recovered correctly.

The transaction log is made up of virtual log files (VLFs). SQL Server can reuse log space when the relevant log records are no longer needed for recovery or other database operations.

Error 9002 occurs when SQL Server cannot allocate additional log space because the log is full and the required space cannot be reused or grown sufficiently.

You might see messages such as:

• The transaction log is full due to LOG_BACKUP.
• The transaction log is full due to ACTIVE_TRANSACTION.
• The transaction log is full due to AVAILABILITY_REPLICA.
• The transaction log is full due to REPLICATION.
• The transaction log is full due to CHECKPOINT.

The reason shown in the error message is a valuable starting point. It tells you what is preventing log truncation or reuse at that moment, although further investigation may still be necessary.

Important distinction: Log truncation marks inactive log space as reusable. It does not shrink the physical .ldf file.

2. Step One: Find Out Why the Log Is Full

Don’t change anything yet. First, collect evidence.

Check transaction log usage

Run this query in the affected database:

USE SalesDB;
GO

SELECT
    total_log_size_in_bytes / 1024.0 / 1024
        AS total_log_size_mb,
    used_log_space_in_bytes / 1024.0 / 1024
        AS used_log_space_mb,
    used_log_space_in_percent,
    log_space_in_bytes_since_last_backup / 1024.0 / 1024
        AS log_generated_since_backup_mb
FROM sys.dm_db_log_space_usage;

This shows the current log size, used space, and log generated since the last log backup.

Check why SQL Server cannot reuse log space

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

The most important column here is log_reuse_wait_desc.

Value	What it indicates	What to investigate
LOG_BACKUP	Log backup is needed to permit reuse	Recovery model and log backup schedule
ACTIVE_TRANSACTION	An active transaction is preventing reuse	Long-running transactions and application sessions
AVAILABILITY_REPLICA	An availability replica is holding up log reuse	Replica connectivity, synchronization, and queues
REPLICATION	Replication-related log processing is pending	Log Reader Agent and replication health
CHECKPOINT	A checkpoint or log-reuse condition needs investigation	Database state, checkpoint activity, and log VLFs
NOTHING	No current log-reuse wait is reported	Check whether the log has already filled, file growth is restricted, or another issue exists

These are diagnostic clues, not automatic instructions to run a particular command. The appropriate action depends on the actual condition.

3. Cause One: Transaction Log Backups Are Missing

This is one of the most common causes of Error 9002.

Suppose your database uses the FULL recovery model, but nobody configured regular transaction log backups.

The transaction log keeps recording changes. Without the required log backups, inactive log records may not be marked reusable, and the log can continue to grow until it reaches its configured limit or the disk runs out of space.

Confirm the recovery model

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

Suppose the result is:

Database: SalesDB
Recovery Model: FULL
Log Reuse Wait: LOG_BACKUP

This strongly suggests you should investigate the transaction log backup schedule.

Immediate fix

If the database is healthy, the disk has enough space, and the backup destination is available, take a transaction log backup:

BACKUP LOG SalesDB
TO DISK = 'D:\SQLBackups\SalesDB_Log.trn'
WITH COMPRESSION, STATS = 5;
GO

Use a unique filename for each backup in a real backup process. Confirm the backup destination, permissions, available disk space, and retention policy before running the command.

Then check log usage again.

Important: A log backup does not necessarily shrink the .ldf file. It makes eligible log space reusable, allowing SQL Server to reuse it for subsequent transactions.

Permanent fix

• Configure regular transaction log backups.
• Choose the interval according to your RPO.
• Monitor backup failures.
• Alert when log usage grows unexpectedly.
• Test restores using the full, differential, and log backup sequence.

For example, if the business can tolerate at most five minutes of data loss, a 30-minute log backup interval is not sufficient on its own to meet that RPO.

Do not switch a production database from FULL to SIMPLE just to clear Error 9002. That changes the recovery strategy and breaks the existing log backup chain for point-in-time recovery. If you deliberately change recovery models, plan the backup strategy accordingly.

4. Cause Two: A Long-Running Transaction Is Holding the Log

Consider this scenario:

A developer starts a transaction, updates millions of rows, and leaves the transaction open. Meanwhile, other application transactions continue to generate log records.

SQL Server may be unable to reuse log space required by that active transaction.

The error may show:

The transaction log for database 'SalesDB'
is full due to 'ACTIVE_TRANSACTION'.

Step 1: Check for an open transaction

USE SalesDB;
GO

DBCC OPENTRAN;
GO

This can identify the oldest active transaction and its associated session information.

Step 2: Inspect active requests

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    r.status,
    r.command,
    r.start_time,
    r.wait_type,
    r.blocking_session_id,
    r.total_elapsed_time,
    txt.text AS sql_text
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE s.is_user_process = 1
ORDER BY r.start_time;

This query is a starting point for identifying active requests. A session can have an open transaction even when it has no currently executing request, so use DBCC OPENTRAN and transaction DMVs as well.

How should you respond?

If an application transaction is still doing useful work, coordinate with the application owner to let it complete if safe.

If the transaction is abandoned or must be cancelled, investigate the session and business impact before terminating it.

KILL 57;

Here, we use 57 is only an example session ID. Never copy this command blindly into production.

Killing a session can initiate a rollback, and rolling back a large transaction can itself take substantial time and generate additional I/O.

Permanent fix: Identify why transactions remain open for too long. Review application transaction handling, batch size, job duration, and error-handling logic.

5. Cause Three: The Disk Is Full or Log Growth Is Restricted

Sometimes SQL Server needs more log space but cannot allocate it.

Possible reasons include:

• The disk containing the log file is full.
• Autogrowth is disabled.
• The file has reached its MAXSIZE.
• The configured growth increment cannot be allocated.
• Storage or permissions prevent file growth.

Check the log file configuration

SELECT
    DB_NAME(database_id) AS database_name,
    name AS logical_file_name,
    physical_name,
    size / 128.0 AS current_size_mb,
    max_size,
    growth,
    is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID(N'SalesDB')
  AND type_desc = 'LOG';

Interpretation:

• current_size_mb shows the current allocated size.
• max_size = -1 means no file-level maximum is specified, though other limits still apply.
• growth = 0 means autogrowth is disabled.
• is_percent_growth = 1 means growth is percentage-based; otherwise, the increment is in 8-KB pages.

Also check actual free space on the volume. A configuration that allows growth cannot overcome a full disk.

What should you do?

If the disk is full, coordinate with the infrastructure team to free or add capacity safely.

If the log has reached a configured limit, consider increasing the limit or the file size when capacity permits.

For example, to increase the existing log file’s maximum to 50 GB:

ALTER DATABASE SalesDB
MODIFY FILE
(
    NAME = N'SalesDB_log',
    MAXSIZE = 51200MB
);
GO

This assumes the logical file name is SalesDB_log. Verify it first, and ensure the volume can accommodate the potential growth.

If the existing file is too small for normal workload demand, consider pre-sizing it and configuring a suitable fixed-size growth increment. Do not simply set the maximum to unlimited without considering the disk capacity and impact on other databases.

Microsoft recommends sizing log growth to the workload rather than relying on frequent small growth events.

6. Cause Four: An Availability Group Replica Is Falling Behind

In an Always On Availability Group, the primary may be unable to reuse log records while required changes have not been hardened on a secondary replica.

You might see:

The transaction log for database 'SalesDB'
is full due to 'AVAILABILITY_REPLICA'.

What should you check?

• Is the secondary replica connected?
• Is data movement suspended?
• Is the send queue growing?
• Is the secondary receiving and hardening log records?
• Is the network experiencing problems?
• Is the secondary storage too slow to keep up?

A secondary replica that falls behind can cause the primary log to retain more records than expected.

Practical response

Investigate replica health and the send queue before changing log settings or removing the database from the Availability Group.

If the secondary is disconnected or data movement is suspended, follow the documented AG recovery procedure for that condition.

Do not remove a replica or suspend protection as a quick fix without understanding the data-loss and recovery consequences. The correct solution is usually to restore healthy data movement or resolve the bottleneck.

7. Cause Five: Replication Is Not Moving Log Records Forward

Transactional replication can also delay log reuse if replicated transactions have not been processed as required.

Check the relevant SQL Server Agent jobs, especially the Log Reader Agent, and review replication monitoring for errors or latency.

The fix may involve resolving an agent failure, connectivity issue, or a backlog of replicated transactions.

Don’t remove replication or alter the database recovery configuration just to release log space. First identify the replication condition preventing progress and resolve it using the appropriate replication troubleshooting procedure.

8. Cause Six: Checkpoint or Other Log-Reuse Conditions

If log_reuse_wait_desc shows CHECKPOINT, investigate checkpoint activity and the state of the database.

For a healthy online database, you can examine the current wait reason and VLF information:

USE SalesDB;
GO

SELECT *
FROM sys.dm_db_log_info(DB_ID(N'SalesDB'));
GO

The DMV returns information about virtual log files. It can help when investigating log structure and why the logical log cannot wrap around and reuse space.

A checkpoint may be appropriate in certain situations, but do not run CHECKPOINT blindly as a universal fix. It will not resolve a long-running transaction, missing log backups, or a blocked availability replica.

If the wait reason is NOTHING but the log remains full, return to the file-size, autogrowth, disk-space, and current workload checks.

9. Should You Shrink the Transaction Log?

This is a common mistake.

Suppose the log file has grown to 80 GB during a large data load. After the operation finishes, only 10 GB is being used.

Should you shrink the file back to 10 GB?

Not automatically.

First determine whether the workload regularly requires large amounts of log space. If it does, shrinking the log may simply force SQL Server to grow it again during the next peak period.

A shrink operation reduces the physical file size; it does not solve the reason log records were being retained.

If the incident caused an unusual one-time growth and the log now has substantial reusable space, a controlled shrink may be reasonable after the underlying issue has been resolved.

Example:

USE SalesDB;
GO

DBCC SHRINKFILE (N'SalesDB_log', 20480);
GO

The target here is 20,480 MB (20 GB), and SalesDB_log must be the actual logical log file name.

Before executing, confirm that the target size can accommodate normal peak workload demand. Monitor subsequent growth and adjust the planned size if necessary.

Microsoft advises against treating shrinking as routine maintenance.

10. A Practical Error 9002 Decision Tree

Use the reason shown in the error message and log_reuse_wait_desc to decide what to investigate first.

Error 9002: Transaction log full

Check log_reuse_wait_desc and log file configuration

LOG_BACKUP

Check recovery model and log backups.

ACTIVE_TRANSACTION

Find and resolve the open transaction.

AVAILABILITY_REPLICA

Investigate replica health and queues.

Disk / size limit

Check free disk space, autogrowth and MAXSIZE.

Resolve the cause → verify reusable space → validate workload

11. How to Prevent Error 9002 in Production

A good prevention strategy includes:

• Schedule transaction log backups at an interval that meets the business RPO.
• Monitor log usage and growth trends.
• Alert on log backup failures.
• Review long-running transactions and application transaction handling.
• Monitor Availability Group synchronization and replication health.
• Pre-size the log for expected peak workload.
• Configure a suitable fixed-size autogrowth increment.
• Monitor disk capacity and the log’s maximum-size setting.
• Test recovery procedures regularly.

A full backup alone does not truncate the transaction log in the FULL recovery model. Regular transaction log backups are required to maintain the log backup chain and allow eligible log space to be reused.

12. Version and Permission Notes

This guide primarily applies to SQL Server 2019, SQL Server 2022, and SQL Server 2025. The core troubleshooting principles are similar across these versions, but permissions and platform-specific behavior can differ.

• Monitoring DMVs may require VIEW SERVER STATE on SQL Server 2019 and earlier, or VIEW SERVER PERFORMANCE STATE for relevant DMVs on SQL Server 2022 and later.
• Backup and database configuration changes require appropriate database or server permissions.
• Coordinate changes to production recovery models, backup schedules, and HA configurations with the DBA and infrastructure teams.

For Azure SQL Database and Azure SQL Managed Instance, use the corresponding platform-specific troubleshooting guidance.

13. Final DBA Checklist

Before closing an Error 9002 incident, confirm:

• The actual cause has been identified.
• Log usage has been rechecked.
• The log can reuse space or grow within available capacity.
• Required backups are succeeding.
• Any long-running transaction has been addressed safely.
• Availability Group or replication health is restored, where applicable.
• The application can perform normal transactions.
• Monitoring and alerts are configured to catch recurrence.
• The incident and permanent fix are documented.

Summary

When SQL Server Error 9002 occurs, the fastest safe fix is not always to increase the log file or shrink it.

Find out why SQL Server cannot reuse log space, resolve that condition, and then verify that the database can continue processing transactions.

That approach fixes the underlying problem instead of merely postponing the next outage.