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

核心工具：DBCC OPENTRAN

快速顯示資料庫中最早的活動事務資訊（SPID、開始時間、LSN、用戶 ID）。語法：DBCC OPENTRAN [ ( database_name ) ] [ WITH NO_INFOMSGS ]。

實戰範例

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=54、Start Time=2010-12-24 14:05:29。

進階診斷：結合 DMV 三步走

1. 會話 ↔ 事務對應

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

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;

3. 事務屬性詳情

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;

適用場景

• 孤立連線：用戶端斷開但事務未結束，佔用鎖、阻塞他人。
• 遺棄事務：應用邏輯漏寫 COMMIT/ROLLBACK，導致記錄檔暴增。
• 分散式事務卡住：MSDTC 協調失敗，狀態停留在 Prepared (5)。

標準處理流程

1. DBCC OPENTRAN 獲取 SPID。
2. 查 DMV 取得 transaction_id 與 SQL 文本。
3. 確認事務屬性（啟動時間、類型、狀態）。
4. 業務判斷：通知開發修正代碼、或謹慎 KILL SPID 釋放資源。

注意事項

• KILL 會回滾未提交工作，可能造成資料不一致，務必先評估影響。
• 長期解決：應用層使用 using (TransactionScope) 確保事務正確結束；大批次拆分小事務；啟用連線池正確釋放連線。