Fix SQL Server Error 9002: Transaction Log Full (ACTIVE_TRANSACTION) with Log Maintenance

 

Introduction

SQL Server Error 9002: The transaction log for database '<database_name>' is full due to 'ACTIVE_TRANSACTION'. occurs when the transaction log cannot grow because of disk space limitations, file size restrictions, or long-running transactions preventing log truncation. The ACTIVE_TRANSACTION reason indicates an open transaction is holding log space. This guide provides code-oriented solutions to resolve Error 9002 and implement transaction log maintenance strategies to prevent recurrence.

---

1. Identify the Cause of the Full Transaction Log

Determine if the log is full due to disk space, file size limits, or an active transaction.

Code-Oriented Solution

Check transaction log usage:

-- View log space usage
DBCC SQLPERF(LOGSPACE);

Identify active transactions:

-- List active transactions
SELECT 
    t.transaction_id,
    t.name AS TransactionName,
    t.transaction_begin_time,
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_tran_session_transactions st ON t.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
WHERE t.transaction_id IS NOT NULL;

Check log file settings:

-- View log file details
SELECT 
    name AS FileName,
    physical_name AS FilePath,
    (size * 8.0 / 1024) AS SizeMB,
    (max_size * 8.0 / 1024) AS MaxSizeMB,
    growth,
    is_percent_growth
FROM sys.database_files
WHERE type_desc = 'LOG';

Check disk space with PowerShell:

# Check disk space
Get-Disk | Get-Partition | Get-Volume | Select-Object DriveLetter, SizeRemaining, Size

Action:

• Note the log file’s current size, max size, and growth settings.

• Identify long-running transactions from sys.dm_tran_active_transactions.

• Check if the disk hosting the log file is full.

Why? Error 9002 occurs when the log cannot grow due to disk space, file size limits, or an uncommitted transaction holding log space.

---

2. Resolve Active Transactions

Close or commit long-running transactions to free log space.

Code-Oriented Solution

Identify and terminate the offending transaction:

-- Find the session ID of the active transaction
SELECT 
    session_id,
    transaction_id,
    transaction_begin_time,
    login_name,
    host_name,
    program_name
FROM sys.dm_tran_session_transactions st
JOIN sys.dm_tran_active_transactions t ON st.transaction_id = t.transaction_id
JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id;

-- Terminate the session (use with caution)
KILL 52; -- Replace 52 with the session_id

Manually commit or rollback if accessible:

-- Example: Commit or rollback a transaction (if you have control)
BEGIN TRANSACTION;
    -- Your transaction code
COMMIT TRANSACTION; -- or ROLLBACK TRANSACTION;

Action:

• Contact the application or user associated with the session (from host_name or program_name) to commit or rollback the transaction.

• If the transaction cannot be committed, use KILL cautiously to avoid data inconsistencies.

• Monitor for orphaned transactions after termination:

-- Check for orphaned transactions
SELECT * FROM sys.dm_tran_database_transactions
WHERE database_id = DB_ID('YourDatabaseName');

Why? The ACTIVE_TRANSACTION reason indicates an open transaction is preventing log truncation, causing Error 9002.

---

3. Truncate or Shrink the Transaction Log

Free log space by truncating or shrinking the log file after resolving active transactions.

Code-Oriented Solution

Check the recovery model:

-- View recovery model
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'YourDatabaseName';

Truncate the log (for SIMPLE recovery model):

-- Truncate log in SIMPLE recovery
USE YourDatabaseName;
GO
CHECKPOINT;
DBCC SHRINKFILE ('YourLogFileName', 100); -- Shrink to 100MB

Backup and truncate the log (for FULL or BULK_LOGGED recovery model):

-- Backup transaction log
BACKUP LOG YourDatabaseName
TO DISK = 'C:\SQLBackups\YourDatabaseName_Log.trn'
WITH INIT;

-- Shrink log file
DBCC SHRINKFILE ('YourLogFileName', 100);

Action:

• If in FULL recovery, perform a transaction log backup before shrinking.

• Avoid frequent shrinking, as it causes fragmentation; use only to resolve immediate issues.

• Verify log size after shrinking:

SELECT 
    name AS FileName,
    (size * 8.0 / 1024) AS SizeMB
FROM sys.database_files
WHERE type_desc = 'LOG';

Why? Truncating and shrinking the log reclaims space held by committed transactions.

---

4. Expand the Transaction Log File

Increase the log file size or allow unlimited growth to accommodate future transactions.

Code-Oriented Solution

Expand the log file:

-- Increase log file size
ALTER DATABASE YourDatabaseName
MODIFY FILE (
    NAME = 'YourLogFileName',
    SIZE = 1024MB, -- New size (e.g., 1GB)
    FILEGROWTH = 100MB
);

Set unlimited growth if restricted:

-- Allow unlimited growth
ALTER DATABASE YourDatabaseName
MODIFY FILE (
    NAME = 'YourLogFileName',
    MAXSIZE = UNLIMITED
);

Action:

• Ensure the disk has sufficient free space before expanding.

• Verify the change:

SELECT 
    name AS FileName,
    (size * 8.0 / 1024) AS SizeMB,
    (max_size * 8.0 / 1024) AS MaxSizeMB
FROM sys.database_files
WHERE type_desc = 'LOG';

Why? Expanding the log file prevents Error 9002 if disk space is available.

---

5. Add a New Log File

If the disk is full, add a new log file on a different disk with available space.

Code-Oriented Solution

Add a new log file:

-- Add new log file to database
ALTER DATABASE YourDatabaseName
ADD LOG FILE (
    NAME = 'YourLogFileName_New',
    FILENAME = 'D:\SQLData\YourLogFileName_New.ldf',
    SIZE = 1024MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 100MB
);

Action:

• Verify the new file:

SELECT 
    name AS FileName,
    physical_name AS FilePath,
    (size * 8.0 / 1024) AS SizeMB
FROM sys.database_files
WHERE type_desc = 'LOG';

• Ensure the new disk has sufficient free space.

Why? Adding a log file on a disk with available space allows continued transaction logging.

---

6. Optimize Transaction Management

Prevent long-running transactions to avoid log growth.

Code-Oriented Solution

Review application code to ensure transactions are short-lived:

using System.Data.SqlClient;

string connString = "Server=sqlserverhostname;Database=YourDatabaseName;Integrated Security=True;";
try {
    using (SqlConnection conn = new SqlConnection(connString)) {
        conn.Open();
        using (SqlTransaction tran = conn.BeginTransaction()) {
            try {
                using (SqlCommand cmd = new SqlCommand("UPDATE YourTable SET Column1 = @Value WHERE ID = @ID", conn, tran)) {
                    cmd.Parameters.AddWithValue("@Value", "Test");
                    cmd.Parameters.AddWithValue("@ID", 1);
                    cmd.ExecuteNonQuery();
                }
                tran.Commit();
            } catch {
                tran.Rollback();
                throw;
            }
        }
    }
} catch (SqlException ex) when (ex.Number == 9002) {
    Console.WriteLine("Error 9002: Transaction log full. Check active transactions or disk space.");
} catch (Exception ex) {
    Console.WriteLine($"Error: {ex.Message}");
}

Action:

• Keep transactions short by committing or rolling back promptly.

• Avoid user input or long-running operations within transactions.

• Monitor transaction duration:

-- Monitor long-running transactions
SELECT 
    session_id,
    transaction_begin_time,
    DATEDIFF(SECOND, transaction_begin_time, GETDATE()) AS DurationSeconds
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_tran_session_transactions st ON t.transaction_id = st.transaction_id;

Why? Long-running transactions prevent log truncation, leading to Error 9002.

---

7. Implement Transaction Log Maintenance

Set up regular log backups and monitoring to prevent log growth issues.

Code-Oriented Solution

Schedule transaction log backups (for FULL or BULK_LOGGED recovery):

-- Schedule log backup
BACKUP LOG YourDatabaseName
TO DISK = 'C:\SQLBackups\YourDatabaseName_Log.trn'
WITH INIT;

Monitor log space usage with a SQL Server Agent job:

-- Create job to monitor log space
USE msdb;
GO
EXEC sp_add_job @job_name = 'MonitorTransactionLog';
EXEC sp_add_jobstep 
    @job_name = 'MonitorTransactionLog',
    @step_name = 'CheckLogSpace',
    @subsystem = 'TSQL',
    @command = 'IF EXISTS (SELECT 1 FROM sys.dm_db_log_space_usage WHERE used_log_space > 80) 
                EXEC msdb.dbo.sp_send_dbmail 
                    @profile_name = ''YourMailProfile'', 
                    @recipients = ''admin@yourdomain.com'', 
                    @subject = ''Transaction Log Alert'', 
                    @body = ''Transaction log usage exceeds 80% in YourDatabaseName.'';';
EXEC sp_add_jobschedule 
    @job_name = 'MonitorTransactionLog',
    @name = 'DailyCheck',
    @freq_type = 4, -- Daily
    @freq_interval = 1,
    @active_start_time = 90000; -- 9:00 AM

Action:

• Schedule log backups every 15–60 minutes for FULL recovery databases.

• Set up alerts for high log usage:

-- Create alert for Error 9002
EXEC msdb.dbo.sp_add_alert 
    @name = N'Transaction Log Full',
    @message_id = 9002,
    @severity = 0,
    @enabled = 1,
    @notification_message = N'Error 9002: Transaction log full. Check active transactions or disk space.',
    @include_event_description_in = 1;

EXEC msdb.dbo.sp_add_notification 
    @alert_name = N'Transaction Log Full',
    @operator_name = N'YourOperatorName',
    @notification_method = 1; -- Email

Why? Regular log backups and monitoring prevent log growth and detect issues early.

---

8. Test with SQLCMD or SSMS

Test database operations to confirm resolution.

Code-Oriented Solution

Test with sqlcmd:

# Test transaction
sqlcmd -S sqlserverhostname -U YourLoginName -P YourPassword -d YourDatabaseName -Q "BEGIN TRANSACTION; UPDATE YourTable SET Column1 = 'Test' WHERE ID = 1; COMMIT;"

In SSMS, run a test transaction:

BEGIN TRANSACTION;
    UPDATE YourTable
    SET Column1 = 'Test'
    WHERE ID = 1;
COMMIT;

Action:

• If Error 9002 persists, recheck active transactions and disk space.

• Update application code to handle log full errors:

using System.Data.SqlClient;

string connString = "Server=sqlserverhostname;Database=YourDatabaseName;Integrated Security=True;";
try {
    using (SqlConnection conn = new SqlConnection(connString)) {
        conn.Open();
        using (SqlTransaction tran = conn.BeginTransaction()) {
            using (SqlCommand cmd = new SqlCommand("UPDATE YourTable SET Column1 = @Value WHERE ID = @ID", conn, tran)) {
                cmd.Parameters.AddWithValue("@Value", "Test");
                cmd.Parameters.AddWithValue("@ID", 1);
                cmd.ExecuteNonQuery();
            }
            tran.Commit();
            Console.WriteLine("Transaction successful!");
        }
    }
} catch (SqlException ex) when (ex.Number == 9002) {
    Console.WriteLine("Error 9002: Transaction log full. Check active transactions or disk space.");
} catch (Exception ex) {
    Console.WriteLine($"Error: {ex.Message}");
}

Why? Testing confirms the log is manageable and transactions can proceed.

---

Conclusion

SQL Server Error 9002 (Transaction log full due to ACTIVE_TRANSACTION) can be resolved by addressing active transactions, truncating or shrinking the log, expanding log files, and implementing maintenance strategies. Use the provided T-SQL, PowerShell, and C# examples to diagnose and fix the issue systematically. 

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone