tempdb与用户数据库事务日志满（Error 9002）排查求助

Let's dive into troubleshooting this transaction log growth issue where you're seeing Error 9002 (The transaction log for database 'abcd' is full due to 'ACTIVE_TRANSACTION') in a snapshot isolation environment. Here’s a structured approach to narrow down the root cause:

1. Track Down Long-Running Active Transactions #

The core error points to an uncommitted transaction blocking log truncation. In snapshot isolation, SQL Server retains row versions until the transaction completes—this means even scheduled log backups can’t reclaim log space tied to these active transactions. Use these queries to identify stuck or long-running transactions:

SELECT 
    t.transaction_id,
    t.transaction_begin_time,
    s.session_id,
    s.login_name,
    s.host_name,
    dest.text AS executing_sql
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
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) dest
WHERE t.transaction_type != 2 -- Exclude system transactions
ORDER BY t.transaction_begin_time;

Look for transactions that started before the first 9002 error (12:03) and are still active. The executing_sql column will show what the transaction is doing—this could be a forgotten uncommitted transaction from a data processing job, or an application session that never closed properly.

2. Inspect Snapshot Isolation Version Store #

Since your database uses snapshot isolation, row versions are stored in tempdb. Tempdb’s log growth is almost always tied to bloat in this version store. Use these queries to assess version store usage:

-- Check total version store size in tempdb (in MB)
SELECT 
    SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_size_MB
FROM sys.dm_db_file_space_usage;

-- Drill into version store entries tied to your user database
SELECT 
    transaction_id,
    database_id,
    page_count,
    min_version_timestamp
FROM sys.dm_tran_version_store
WHERE database_id = DB_ID('abcd');

A large version store indicates long-running transactions are holding onto old row versions, which blocks log truncation in both your user database and tempdb.

3. Validate Transaction Log Backup Effectiveness #

Even though you’re backing up logs every 15 minutes, confirm backups are actually completing and triggering log truncation. First, check why the log can’t be reused:

SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = 'abcd';

If log_reuse_wait_desc returns ACTIVE_TRANSACTION, this confirms the issue is tied to uncommitted transactions. If it returns LOG_BACKUP, your backups might be failing silently—check SQL Server’s error log for backup failures around 12:01 (the time of the last backup before the first error).

You can also verify log space usage before and after backups with:

DBCC SQLPERF(LOGSPACE);

If the used log space doesn’t drop after a backup, truncation isn’t happening as expected.

4. Audit Scheduled Jobs & Data Processing Tasks #

You mentioned rebuild indexes and data processing tasks completed successfully, but some jobs might leave transactions open (e.g., a job that starts a transaction but doesn’t commit or rollback on error). Cross-reference job execution times (from msdb.dbo.sysjobhistory) with the active transactions you found earlier. Look for jobs that ran around 12:00–12:03 and check if their associated sessions have open transactions.

5. Analyze Transaction Log VLFs & Truncation Points #

Check the Virtual Log Files (VLFs) in your user database to see which parts of the log are marked as active (and thus can’t be truncated):

SELECT 
    vlf_id,
    status, -- 1 = Active, 0 = Inactive
    size_bytes / 1024 / 1024 AS vlf_size_MB,
    end_lsn
FROM sys.dm_db_log_info(DB_ID('abcd'))
ORDER BY vlf_id;

If a large number of VLFs are active, it means an old transaction is preventing truncation from moving forward through the log.

6. Check Application-Level Transaction Behavior #

Don’t overlook application code issues. Applications might start transactions but fail to commit them if there’s an unhandled exception, or keep sessions open with pending transactions. Use the session login/host info from step 1 to identify the source of the long transaction, then collaborate with the application team to review code for transaction handling gaps.

内容的提问来源于stack exchange，提问作者Nish