SQL Server 2008 事務監控實戰：DBCC OPENTRAN 與 DMV 追蹤長時間活動事務

為何要監控活動事務？

• 長時間未提交/回滾的事務會導致交易記錄無法截斷，甚至填滿記錄檔導致資料庫停擺。
• 孤立連線（用戶端已斷開但事務仍開啟）佔用鎖、阻塞其他會話。
• 應用程式邏輯錯誤導致事務「被遺棄」（打開但從未 COMMIT/ROLLBACK）。

一、DBCC OPENTRAN：快速定位最早活動事務

CREATE TABLE T_Product(PKID int, PName Nvarchar(50));
GO
BEGIN TRAN
INSERT INTO T_Product VALUES (101, N'嫦娥四號');
GO
DBCC OPENTRAN;
ROLLBACK TRAN;
GO
DROP TABLE T_Product;
GO

輸出關鍵欄位：

• SPID：伺服器進程 ID（如 54）。
• Start Time：事務開始時間（如 2010-12-24 14:50:15）。
• LSN：記錄序號，供進一步分析。

若無活動事務，僅回傳資訊性訊息。

二、結合 DMV 深度挖掘事務細節

步驟 1：取得會話 ID 與事務 ID 對應

SELECT session_id, transaction_id, is_user_transaction, is_local
FROM sys.dm_tran_session_transactions
WHERE is_user_transaction = 1;

結果例：session_id=54, transaction_id=489743。

步驟 2：查詢該會話最近執行的 SQL 文本

SELECT s.text
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) s
WHERE session_id = 54;

或使用 sys.dm_exec_requests 取得目前執行陳述式。

步驟 3：透過事務 ID 了解事務屬性

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, not started'
         WHEN 2 THEN 'Active'
         WHEN 3 THEN 'Ended (read-only)'
         WHEN 4 THEN 'Commit initiated (distributed)'
         WHEN 5 THEN 'Prepared, awaiting resolution'
         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;

可得知事務啟動時間、類型、目前狀態（如 Active）。

三、標準排查流程總結

1. 執行 DBCC OPENTRAN 獲取最早活動事務的 SPID。
2. 查 sys.dm_tran_session_transactions 取得對應 transaction_id。
3. 用 sys.dm_exec_connections + sys.dm_exec_sql_text 找出該 SPID 最近執行的 SQL。
4. 查 sys.dm_tran_active_transactions 確認事務持續時間、類型、狀態。
5. 根據業務判斷：若為應用遺棄事務，聯繫開發修正代碼（補上 COMMIT/ROLLBACK 或縮短事務範圍）；若為阻塞鏈頭，考慮 KILL（謹慎）。

四、常見場景與對策