前言

死鎖是數據庫並發環境中常見的問題，及時捕獲並記錄死鎖信息對於問題排查至關重要。本文介紹兩種在 SQL Server 中實現死鎖監控的成熟方案。

方案一：SQL Server Agent 警報 + 作業

步驟 1：啟用追蹤標志

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

標志說明：

• 3605：將 DBCC 結果輸出到錯誤日誌
• 1204：返回參與死鎖的鎖資源、類型及受影響命令
• 1222：以 XML 格式返回詳細死鎖信息（SQL 2005+ 支持）
• -1：全局生效，直到服務重啟

為了持久化，可在 SQL Server 配置管理器中添加啟動參數 /T3605 /T1204 /T1222。

步驟 2：建立死鎖記錄表

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

步驟 3：建立清理作業

創建作業 DeadLockJob，步驟命令如下：

IF OBJECT_ID('tempdb.dbo.#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
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
• 響應：執行 DeadLockJob

方案二：服務端追蹤

步驟 1：創建追蹤腳本

DECLARE @rc INT, @TraceID INT, @maxfilesize BIGINT;
SET @maxfilesize = 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 事件
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;
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 打開： 文件 → 打開追蹤文件，可視化查看死鎖圖

總結

方案一適合輕量級監控，依賴 Agent 服務；方案二性能開銷更低，適合高並發環境，且能獲得完整的死鎖圖信息。建議根據業務場景選擇。