萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 強制斷開資料庫連接腳本:KILL SPID 批次處理與建庫腳本整合

執行 `DROP DATABASE`、`ALTER DATABASE SET SINGLE_USER`、還原資料庫時,若存在活躍連接會報錯失敗。本文提供**標準游標版**、**集合版 (高效)**、**`ALTER DATABASE ... ROLLBACK IMMEDIATE` (現代最佳)** 三種強制斷開連接方案,並示範整合至建庫部署腳本的最佳實踐。

方案一:經典游標版 (原文代碼,兼容 SQL 2000+)

DECLARE @spid INT;
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT spid FROM sys.sysprocesses 
    WHERE dbid = DB_ID('Your_Database_Name') AND spid <> @@SPID; -- 排除自身
    
OPEN cur;
FETCH NEXT FROM cur INTO @spid;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('KILL ' + @spid);  -- 隱式轉換 INT→VARCHAR
    FETCH NEXT FROM cur INTO @spid;
END
CLOSE cur; DEALLOCATE cur;

方案二:集合版 (無游標、效能更高、SQL 2005+)

DECLARE @killCmd NVARCHAR(MAX) = N'';
SELECT @killCmd += N'KILL ' + CAST(spid AS NVARCHAR(10)) + N'; '
FROM sys.dm_exec_sessions 
WHERE database_id = DB_ID('Your_Database_Name') 
  AND session_id <> @@SPID
  AND is_user_process = 1; -- 僅殺用戶進程,排除系統進程

IF @killCmd <> N'' EXEC sp_executesql @killCmd;
  • 使用 `sys.dm_exec_sessions` (DMV) 替代舊 `sysprocesses`。
  • 單次動態 SQL 批次執行,減少往返開銷。
  • 過濾 `is_user_process=1` 避免誤殺系統背景任務 (如 Ghost Cleanup, Checkpoint)。

方案三:ALTER DATABASE ... ROLLBACK IMMEDIATE (SQL 2005+、最佳實踐)

-- 單條語句原子性完成:切換單用戶模式並強制回滾所有未完成交易、斷開所有連接
ALTER DATABASE [Your_Database_Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- 執行 DROP / RESTORE / ALTER 等獨佔操作
-- DROP DATABASE [Your_Database_Name];
-- 或 RESTORE DATABASE ...
-- 操作完成後恢復多用戶
ALTER DATABASE [Your_Database_Name] SET MULTI_USER;
  • **原子性**:無競態條件 (游標方案中,新連接可能在 KILL 間隙建立)。
  • **內建交易回滾**:`ROLLBACK IMMEDIATE` 確保未提交交易安全回滾。
  • **無需動態 SQL / 游標 / xp_cmdshell**,最安全、最標準、效能最好。
  • **唯一前提**:呼叫者需 `ALTER DATABASE` 權限 (`db_owner`/`sysadmin`)。

四、 整合至建庫部署腳本最佳實踐

方案 A:SQLCMD 模式腳本 (.sql 檔) —— 推薦

:setvar DbName "MyAppDb"
:setvar DataPath "D:\SQLData"
:setvar LogPath  "L:\SQLLog"

-- 1. 強制斷開並刪除舊庫 (冪等)
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = '$(DbName)')
BEGIN
    ALTER DATABASE [$(DbName)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DbName)];
END
GO

-- 2. 建立新資料庫 (指定檔案路徑、初始大小、成長增量)
CREATE DATABASE [$(DbName)]
ON PRIMARY 
( NAME = N'$(DbName)_Data', FILENAME = '$(DataPath)\$(DbName).mdf', SIZE = 512MB, FILEGROWTH = 256MB )
LOG ON
( NAME = N'$(DbName)_Log',  FILENAME = '$(LogPath)\$(DbName).ldf', SIZE = 128MB, FILEGROWTH = 64MB );
GO

-- 3. 設定資料庫選項
ALTER DATABASE [$(DbName)] SET RECOVERY FULL; 
ALTER DATABASE [$(DbName)] SET READ_COMMITTED_SNAPSHOT ON; -- 推薦啟用 RCSI 減少阻塞
ALTER DATABASE [$(DbName)] SET MULTI_USER;
GO

執行:`sqlcmd -S Server -i DeployDb.sql -v DbName=MyAppDb` (支援變數替換,適合 CI/CD)。

方案 B:批次檔 + osql/sqlcmd (舊式,原文風格)

:: deploy.bat
@echo off
set SERVER=%1
set USER=%2
set PWD=%3
set DBNAME=%4

sqlcmd -S %SERVER% -U %USER% -P %PWD% -Q "IF EXISTS (SELECT 1 FROM sys.databases WHERE name='%DBNAME%') BEGIN ALTER DATABASE [%DBNAME%] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [%DBNAME%]; END"
sqlcmd -S %SERVER% -U %USER% -P %PWD% -i CreateSchema.sql -v DbName=%DBNAME%

五、 進階場景:RCSI / 可用性群組 / 複寫 下的連接斷開

  • RCSI (Read Committed Snapshot Isolation)**:`ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON/OFF` 需獨佔資料庫,**同樣需先 `SET SINGLE_USER WITH ROLLBACK IMMEDIATE`**。
  • Always On AG 主資料庫**:無法 `DROP`,需先移除可用性群組 (`ALTER AVAILABILITY GROUP ... REMOVE DATABASE`)。
  • 複寫發佈資料庫**:需先移除發佈 (`sp_droppublication` / `sp_replicationdboption`)。
  • 資料庫鏡像**:需先中斷鏡像 (`ALTER DATABASE ... SET PARTNER OFF`)。

結語

**棄用游標 KILL 循環,擁抱 `ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE`**。這是 SQL Server 2005 以來官方推薦、原子性、安全、高效的標準作法。部署腳本採用 SQLCMD 變數化模板,納入版控與 CI/CD 流程,實現資料庫結構變更的自動化、可重複、可審計。

copyright © 萬盛學電腦網 all rights reserved