SQL Server 死鎖捕獲與記錄完整指南：兩種實戰方案

方案一：SQL Server Agent 警報 + 作業（適用 2005+）

步驟 1：啟用追蹤旗標

DBCC TRACEON (3605, 1204, 1222, -1);

• 3605：將 DBCC 輸出導向錯誤日誌
• 1204：記錄死鎖資源、鎖類型與受影響命令
• 1222：以 XML 格式記錄更詳細死鎖資訊（2005+）
• -1：全域生效，重啟後失效；需在啟動參數加入 /T3605 /T1204 /T1222 持久化

步驟 2：建立死鎖記錄表

USE [YourDB];
CREATE TABLE DeadLockLog (
    id INT IDENTITY(1,1) PRIMARY KEY,
    LogDate DATETIME,
    ProcessInfo VARCHAR(10),
    ErrorText VARCHAR(MAX)
);

步驟 3：建立 Agent 作業

-- 作業步驟命令
IF OBJECT_ID('tempdb..#ErrorLog') IS NOT NULL DROP TABLE #ErrorLog;
CREATE TABLE #ErrorLog (Id INT IDENTITY(1,1), a DATETIME, b VARCHAR(10), c VARCHAR(MAX));
INSERT INTO #ErrorLog EXEC master.dbo.sp_readerrorlog;
INSERT INTO DeadLockLog (LogDate, ProcessInfo, ErrorText)
SELECT a, b, c FROM #ErrorLog
WHERE Id >= (SELECT MAX(Id) FROM #ErrorLog WHERE c LIKE '%Deadlock encountered%');
DROP TABLE #ErrorLog;

步驟 4：建立警報

• 類型：SQL Server 效能條件警報
• 物件：SQLServer:Locks
• 計數器：Number of Deadlocks/sec
• 實例：_Total
• 條件：高於 0
• 回應：執行上述作業

方案二：伺服器端追蹤（效能更佳，支援圖形化分析）

步驟 1：建立並啟動追蹤

DECLARE @rc INT, @TraceID INT, @maxfilesize BIGINT = 5;
EXEC @rc = sp_trace_create @TraceID OUTPUT, 0, N'E:/DbLog/deadlockdetect', @maxfilesize, NULL;
IF (@rc != 0) GOTO error;

DECLARE @on BIT = 1;
-- 148 = Locks:Deadlock Graph, 相關欄位請參考 sys.trace_events/columns
EXEC sp_trace_setevent @TraceID, 148, 12, @on; -- SPID
EXEC sp_trace_setevent @TraceID, 148, 11, @on; -- LoginName
EXEC sp_trace_setevent @TraceID, 148, 4,  @on; -- DatabaseID
EXEC sp_trace_setevent @TraceID, 148, 14, @on; -- StartTime
EXEC sp_trace_setevent @TraceID, 148, 26, @on; -- ServerName
EXEC sp_trace_setevent @TraceID, 148, 64, @on; -- SessionLoginName
EXEC sp_trace_setevent @TraceID, 148, 1,  @on; -- TextData (XML 死鎖圖)

EXEC sp_trace_setstatus @TraceID, 1; -- 啟動
SELECT TraceID = @TraceID; -- 記下 ID 供後續控制
GOTO finish;
error: SELECT ErrorCode = @rc;
finish: GO

步驟 2：暫停/停止追蹤

-- 暫停
EXEC sp_trace_setstatus @TraceID, 0;
-- 停止並關閉
EXEC sp_trace_setstatus @TraceID, 2;

步驟 3：查看結果

• T-SQL：SELECT * FROM fn_trace_gettable('E:/DbLog/deadlockdetect.trc', 1);，TextData 欄位為 XML 死鎖圖
• Profiler：檔案 → 開啟追蹤檔案，直接以圖形化介面分析

方案比較與建議

建議：生產環境優先採用方案二，搭配定時作業清理舊追蹤檔；方案一適合快速臨時排查。