SQL Server 鎖定逾時解決方案：殺掉資料庫進程與強制還原腳本

問題背景

遇到「已超過鎖定要求逾時時段」錯誤，通常是因為長時間執行的交易阻塞其他 Session。需要快速釋放鎖定、終止阻塞進程，甚至強制還原資料庫。

儲存程序：終止指定資料庫所有使用者進程

CREATE PROC Sp_KillAllProcessInDB
    @DbName VARCHAR(100)
AS
BEGIN
    IF DB_ID(@DbName) IS NULL
    BEGIN
        PRINT 'Database does not exist';
        RETURN;
    END

    DECLARE @spId VARCHAR(30);
    DECLARE TmpCursor CURSOR FOR
        SELECT 'KILL ' + CAST(spid AS VARCHAR) AS spId
        FROM master..SysProcesses
        WHERE DB_NAME(dbid) = @DbName
          AND spid <> @@SPID
          AND dbid <> 0;

    OPEN TmpCursor;
    FETCH NEXT FROM TmpCursor INTO @spId;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC(@spId);
        FETCH NEXT FROM TmpCursor INTO @spId;
    END

    CLOSE TmpCursor;
    DEALLOCATE TmpCursor;
END
GO

-- 執行範例
EXEC dbo.Sp_KillAllProcessInDB 'MyDatabase';

進階版：僅終止阻塞進程

CREATE PROC Sp_KillBlockingProcesses
    @DbName VARCHAR(100)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) = N'';
    
    SELECT @sql += 'KILL ' + CAST(spid AS VARCHAR) + '; '
    FROM master..SysProcesses
    WHERE DB_NAME(dbid) = @DbName
      AND spid <> @@SPID
      AND blocked <> 0  -- 只殺阻塞別人的進程
      AND dbid <> 0;
      
    IF @sql <> N''
        EXEC sp_executesql @sql;
    ELSE
        PRINT 'No blocking processes found.';
END

強制還原資料庫腳本

當資料庫處於使用中無法還原時：

-- 1. 設定單人模式，強制斷開所有連線
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- 2. 執行還原
RESTORE DATABASE MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase.bak'
WITH REPLACE, STATS = 10;

-- 3. 恢復多用戶模式
ALTER DATABASE MyDatabase SET MULTI_USER;

常用診斷查詢

查看目前阻塞鏈

SELECT 
    r.session_id AS BlockedSPID,
    r.blocking_session_id AS BlockerSPID,
    DB_NAME(r.database_id) AS DatabaseName,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text AS BlockedQuery,
    bt.text AS BlockerQuery
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
LEFT JOIN sys.dm_exec_requests br ON r.blocking_session_id = br.session_id
OUTER APPLY sys.dm_exec_sql_text(br.sql_handle) bt
WHERE r.blocking_session_id <> 0;

查看鎖定資源詳情

SELECT 
    DB_NAME(resource_database_id) AS DatabaseName,
    resource_type,
    resource_description,
    request_mode,
    request_type,
    request_status,
    request_session_id AS SPID
FROM sys.dm_tran_locks
WHERE request_status = 'WAIT';

預防措施

• 避免長時間交易：拆分大批次操作、優化查詢、適當索引
• 設定 LOCK_TIMEOUT：SET LOCK_TIMEOUT 5000 (毫秒)，避免無限等待
• 使用 WITH (NOLOCK) 或 READ UNCOMMITTED 隔離級別（僅限可容忍髒讀的報表查詢）
• 啟用 READ_COMMITTED_SNAPSHOT：資料庫層級設定，讀取不阻塞寫入

結語

殺進程是治標，優化查詢與交易設計才是治本。建立完善的監控告警機制，在阻塞發生初期自動通知 DBA 介入，才能從根本上保障系統穩定。