SQL Server 死結分析與排查實戰：追蹤旗標 1204、系統預存程序 sp_who_lock 完整指南

死結不會像 Oracle 自動生成追蹤檔，需主動啟用追蹤旗標 1204 記錄死結圖到錯誤日誌，並結合 DBCC PAGE 定位鎖定資源對應表。本文提供改良版系統預存程序 sp_who_lock，可即時顯示阻塞鏈與引發死結的 SPID 及其執行的 SQL 陳述式，並說明 SET LOCK_TIMEOUT 限制等待時間。

死結追蹤啟用與解讀

DBCC TRACEON (1204, -1); -- 全域啟用死結追蹤
DBCC TRACESTATUS(-1);    -- 查看啟用狀態
DBCC TRACEOFF (1204, -1); -- 關閉

錯誤日誌輸出含節點、資源、模式、SPID、隔離等級等資訊，需對照線上叢書解讀。

定位鎖定頁面對應物件

DBCC TRACEON (3604);
DBCC PAGE (db_id, file_id, page_id); -- 輸出頁面內容含物件 ID
DBCC TRACEOFF (3604);

即時阻塞/死結監控預存程序 sp_who_lock

CREATE PROCEDURE sp_who_lock AS
BEGIN
  -- 建立暫存表收集 sysprocesses 阻塞鏈
  -- 迴圈輸出每個被阻塞 SPID 及阻塞者 SPID
  -- 呼叫 DBCC INPUTBUFFER(@bl) 顯示阻塞者當前 SQL 陳述式
END

執行 sp_who_lock 即可快速定位問題進程與 SQL，比 sp_who/sp_lock 更直觀。

鎖定超時保護

SET LOCK_TIMEOUT 900000; -- 15 分鐘，避免無限期阻塞佔用資源