Fix SQL Server Transaction Log Full — Free Up Log Space Fast

What Causes the Transaction Log to Fill Up?

The SQL Server transaction log records every modification made to a database — every INSERT, UPDATE, DELETE, index rebuild, and schema change. The log is designed to be reused in a circular fashion: once SQL Server confirms it no longer needs a portion of the log for recovery or replication, it marks that portion as inactive and reuses it.

The log becomes full and triggers Error 9002 when SQL Server cannot reuse the inactive portions fast enough to keep up with incoming writes. The exact reason why log space can't be reused is shown in the log_reuse_wait_desc column of sys.databases:

-- Find out WHY the log is full (run immediately when you hit Error 9002)
SELECT
    name                AS DatabaseName,
    log_reuse_wait_desc AS WhyLogCannotBeReused,
    CAST(log_size_mb AS decimal(10,1)) AS LogSizeMB,
    CAST(log_used_mb AS decimal(10,1)) AS LogUsedMB
FROM sys.dm_db_log_space_usage CROSS JOIN
    (SELECT SUM(size) * 8.0 / 1024 AS log_size_mb,
            SUM(FILEPROPERTY(name, 'SpaceUsed')) * 8.0 / 1024 AS log_used_mb
     FROM sys.database_files WHERE type_desc = 'LOG') lf
JOIN sys.databases ON database_id = DB_ID();

Immediate Fix: Log Backup + DBCC SHRINKFILE

If log_reuse_wait_desc shows LOG_BACKUP, this two-step process will free log space immediately without any data loss:

-- Transaction log backup — frees inactive log space
BACKUP LOG [YourDatabaseName]
TO DISK = N'D:\Backups\YourDatabase_log_20260701.bak'
WITH COMPRESSION, STATS = 10;
GO

-- Check remaining log space after backup
DBCC SQLPERF(LOGSPACE);

-- Find the logical name of your log file
SELECT name, physical_name, type_desc
FROM sys.database_files
WHERE type_desc = 'LOG';

-- Shrink log file to 256 MB (adjust target size as needed)
USE [YourDatabaseName];
GO
DBCC SHRINKFILE (N'YourDatabase_log', 256);
GO

-- Verify new size
DBCC SQLPERF(LOGSPACE);

Emergency Option: Switch to Simple Recovery Model

If you cannot take a log backup immediately (e.g., no prior full backup exists), you can switch the database to SIMPLE recovery model as an emergency measure. In SIMPLE mode, SQL Server automatically truncates the log at every checkpoint, eliminating the need for log backups.

-- EMERGENCY ONLY: Switch to SIMPLE recovery model
-- WARNING: You lose point-in-time restore capability after this change
ALTER DATABASE [YourDatabaseName]
SET RECOVERY SIMPLE;
GO

-- Force a checkpoint to truncate the log immediately
USE [YourDatabaseName];
CHECKPOINT;
GO

-- Now shrink the freed space
DBCC SHRINKFILE (N'YourDatabase_log', 256);
GO

-- IMPORTANT: Switch back to FULL when done and take a full backup immediately
ALTER DATABASE [YourDatabaseName]
SET RECOVERY FULL;
GO
BACKUP DATABASE [YourDatabaseName]
TO DISK = N'D:\Backups\YourDatabase_full_afterchange.bak';

Prevent It: Schedule Regular Log Backups with SQL Agent

The single most effective way to prevent Error 9002 is to schedule automated transaction log backups using SQL Server Agent. For a busy OLTP database, log backups every 15–30 minutes are standard. Here is a complete SQL Agent job script you can run immediately in SSMS:

USE msdb;
GO

EXEC sp_add_job
    @job_name = N'Transaction Log Backup - YourDatabase',
    @enabled = 1,
    @description = N'Backs up transaction log every 15 minutes to prevent log full errors';

EXEC sp_add_jobstep
    @job_name = N'Transaction Log Backup - YourDatabase',
    @step_name = N'Backup Log',
    @command = N'BACKUP LOG [YourDatabaseName]
TO DISK = N''D:\Backups\YourDatabase_log_'' +
          REPLACE(CONVERT(varchar,GETDATE(),112),'''','''') + ''_'' +
          REPLACE(CONVERT(varchar,GETDATE(),108),'':'','''') + ''.bak''
WITH COMPRESSION, STATS = 10;';

EXEC sp_add_schedule
    @schedule_name = N'Every 15 Minutes',
    @freq_type = 4,            -- Daily
    @freq_interval = 1,
    @freq_subday_type = 4,    -- Minutes
    @freq_subday_interval = 15;

EXEC sp_attach_schedule
    @job_name = N'Transaction Log Backup - YourDatabase',
    @schedule_name = N'Every 15 Minutes';

EXEC sp_add_jobserver
    @job_name = N'Transaction Log Backup - YourDatabase';
GO

Understanding SQL Server Error 9002 in Detail

Error 9002 is raised at severity level 17 (resource error), which means SQL Server cannot complete the requested operation due to a resource constraint — in this case, available log space. Every write statement executed after Error 9002 is raised will immediately fail with the same error until log space is freed.

The parenthetical value in the error message — e.g., "due to 'LOG_BACKUP'" — is the log_reuse_wait_desc from sys.databases and directly tells you what is preventing log reuse. Here is the full reference:

Frequently Asked Questions