SQL Server 查找長時間未提交事務的監控腳本

危害分析

• 阻塞其他事務 (鎖持有不釋放)
• 導致事務日誌無法截斷，磁碟暴漲
• 引發鎖升級、死鎖、連接池耗盡

核心監控查詢

-- 1. 活躍事務概覽
SELECT 
    at.transaction_id,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, GETDATE()) AS duration_sec,
    at.transaction_type,
    at.transaction_state,
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    t.text AS last_sql
FROM sys.dm_tran_active_transactions at
JOIN sys.dm_tran_session_transactions st ON at.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.most_recent_sql_handle) t
WHERE at.transaction_type <> 2  -- 排除系統事務
ORDER BY at.transaction_begin_time;

-- 2. 長事務告警 (超過 60 秒)
SELECT * FROM (
    -- 上述查詢
) t WHERE duration_sec > 60;

-- 3. 事務持有的鎖詳情
SELECT 
    tl.resource_type,
    tl.resource_database_id,
    tl.resource_associated_entity_id,
    tl.request_mode,
    tl.request_status,
    OBJECT_NAME(tl.resource_associated_entity_id) AS object_name
FROM sys.dm_tran_locks tl
JOIN sys.dm_tran_active_transactions at ON tl.request_owner_id = at.transaction_id
WHERE at.transaction_begin_time < DATEADD(minute, -5, GETDATE());

自動化監控作業

-- SQL Agent Job 步驟
IF EXISTS (
    SELECT 1 FROM sys.dm_tran_active_transactions at
    JOIN sys.dm_tran_session_transactions st ON at.transaction_id = st.transaction_id
    WHERE at.transaction_begin_time < DATEADD(minute, -10, GETDATE())
      AND at.transaction_type <> 2
)
BEGIN
    -- 發送郵件告警
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'DBA_Alerts',
        @recipients = 'dba@company.com',
        @subject = 'SQL Server 長事務告警',
        @body = '檢測到超過 10 分鐘未提交事務，請立即處理。',
        @query = '上述監控查詢',
        @attach_query_result_as_file = 1;
END

常見成因與對策

緊急處理流程

1. 確認事務 ID (transaction_id)
2. 評估業務影響：SELECT * FROM sys.dm_exec_requests WHERE transaction_id = ?
3. 若確認可終止：KILL session_id (將觸發回滾，耗時與事務已做工作量成正比)
4. 監控回滾進度：KILL session_id WITH STATUSONLY
5. 事後複盤：補全事務邊界、增加超時機制、完善監控