未提交事務會阻塞其他會話、膨脹交易記錄檔、阻礙記錄檔截斷。本文示範用 DBCC OPENTRAN 定位最早活動事務,再結合 DMV 鑽取具體 SQL 與會話資訊。
DBCC OPENTRAN;
輸出關鍵欄位:
SELECT session_id, transaction_id, is_user_transaction, is_local
FROM sys.dm_tran_session_transactions
WHERE is_user_transaction = 1;
SELECT st.text
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) st
WHERE c.session_id = 54; -- 替換為 Step A 取得的 SPID
SELECT
transaction_begin_time,
CASE transaction_type
WHEN 1 THEN 'Read/Write' WHEN 2 THEN 'Read-Only'
WHEN 3 THEN 'System' WHEN 4 THEN 'Distributed'
END AS tran_type,
CASE transaction_state
WHEN 0 THEN 'Not initialized' WHEN 1 THEN 'Initialized'
WHEN 2 THEN 'Active' WHEN 3 THEN 'Ended (Read-Only)'
WHEN 4 THEN 'Commit initiated' WHEN 5 THEN 'Prepared'
WHEN 6 THEN 'Committed' WHEN 7 THEN 'Rolling back'
WHEN 8 THEN 'Rolled back'
END AS tran_state
FROM sys.dm_tran_active_transactions
WHERE transaction_id = 489743; -- 替換為 Step A 取得的 transaction_id
-- 找出所有運行超過 30 分鐘的用戶事務
SELECT
tat.transaction_id,
tat.transaction_begin_time,
DATEDIFF(MI, tat.transaction_begin_time, GETDATE()) AS duration_min,
tst.session_id,
st.text AS last_sql
FROM sys.dm_tran_active_transactions tat
JOIN sys.dm_tran_session_transactions tst ON tat.transaction_id = tst.transaction_id
CROSS APPLY sys.dm_exec_connections c ON tst.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) st
WHERE tat.transaction_type = 1
AND tat.transaction_state = 2
AND DATEDIFF(MI, tat.transaction_begin_time, GETDATE()) > 30;
CHECKPOINT、調整隔離等級。KILL 釋放鎖與記錄檔空間。DBCC OPENTRAN 定期巡檢 + DMV 長事務查詢 → 郵件/Slack 通知。掌握此流程,可在事務造成嚴重阻塞前主動介入。