SQL Server 查詢與解除鎖定表格：DMV 與傳統方法對照

快速查詢被鎖定表格

方法一：傳統 SysProcesses（SQL 2000/2005 相容）

SELECT spid
FROM master..sysprocesses
WHERE db_name(dbid) = 'YourDatabase'
  AND spid <> @@SPID
  AND dbid <> 0;

方法二：現代 DMV（SQL 2005+ 推薦）

SELECT 
    request_session_id AS spid,
    OBJECT_NAME(resource_associated_entity_id) AS tableName
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT';

說明： spid 為鎖定進程 ID，tableName 為被鎖定表名。

解除鎖定（Kill 進程）

單一進程終止

DECLARE @spid INT = 57;  -- 目標 SPID
DECLARE @sql VARCHAR(1000) = 'KILL ' + CAST(@spid AS VARCHAR);
EXEC(@sql);

批次終止資料庫所有使用者連線

DECLARE @kill VARCHAR(MAX) = '';
SELECT @kill = @kill + 'KILL ' + CAST(spid AS VARCHAR) + '; '
FROM master..sysprocesses
WHERE dbid = DB_ID('YourDatabase') AND spid <> @@SPID;
EXEC(@kill);

進階診斷：鎖鏈與阻塞分析

-- 檢視阻塞鏈
SELECT 
    blocking_session_id AS BlockerSPID,
    session_id AS VictimSPID,
    wait_type,
    wait_time_ms,
    wait_resource
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id > 0;

注意事項

• 謹慎使用 KILL： 會導致交易回滾，長時間交易回滾可能更久
• 優先嘗試： 通知應用端正常結束交易、優化長時間查詢
• 權限需求： 需 PROCESSADMIN 或 SYSADMIN 角色

總結

現代環境優先使用 sys.dm_tran_locks 定位鎖定表格與進程，搭配 KILL 解除阻塞。建議建立監控儀表板自動偵測長時間阻塞並告警。