執行 `DROP DATABASE`、`ALTER DATABASE SET SINGLE_USER`、還原資料庫時,若存在活躍連接會報錯失敗。本文提供**標準游標版**、**集合版 (高效)**、**`ALTER DATABASE ... ROLLBACK IMMEDIATE` (現代最佳)** 三種強制斷開連接方案,並示範整合至建庫部署腳本的最佳實踐。
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;
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;
-- 單條語句原子性完成:切換單用戶模式並強制回滾所有未完成交易、斷開所有連接
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;
: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)。
:: 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%
**棄用游標 KILL 循環,擁抱 `ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE`**。這是 SQL Server 2005 以來官方推薦、原子性、安全、高效的標準作法。部署腳本採用 SQLCMD 變數化模板,納入版控與 CI/CD 流程,實現資料庫結構變更的自動化、可重複、可審計。