// SQL Server error reference

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
Error message
The transaction log for database '%ls' is full due to '%ls' and the holdup lsn is %S_LSN.

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.
  • REPLICATION or AVAILABILITY_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

  1. See why the log can't be reused.

    SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = N'SalesDb';
  2. For LOG_BACKUP, take a log backup (and schedule them).

    BACKUP LOG SalesDb TO DISK = N'D:\Backup\SalesDb_log.trn';
  3. For ACTIVE_TRANSACTION, find the oldest open transaction and get it committed or rolled back.

    DBCC OPENTRAN (N'SalesDb');
  4. 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);
  5. 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;
  6. 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

More memory, log and storage errors

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.

// fewer errors, faster fixes

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.