萬盛學電腦網

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

SQL Server 資料庫從單一使用者模式切回多使用者模式腳本

問題情境

執行資料庫複製/還原操作後,資料庫卡在 SINGLE_USER 模式(物件總管顯示「單一使用者」),導致正常連線被阻擋。需先踢除佔用連線,再切回 MULTI_USER。

標準解決腳本

USE master;
GO

DECLARE @SQL NVARCHAR(MAX) = N'';

-- 1. 產生 KILL 指令針對目標資料庫的所有 SPID
SELECT @SQL = @SQL + N'; KILL ' + CAST(spid AS NVARCHAR(10))
FROM sys.sysprocesses
WHERE dbid = DB_ID(N'你的資料庫名稱');

-- 2. 執行批次 KILL
EXEC sp_executesql @SQL;

-- 3. 切換回多使用者模式
ALTER DATABASE [你的資料庫名稱] SET MULTI_USER;
GO

關鍵點說明

  • sys.sysprocesses 為相容性檢視,新版建議改用 sys.dm_exec_sessions + sys.dm_exec_connections。
  • DB_ID() 確保只殺目標資料庫的連線,避免誤傷。
  • 使用動態 SQL 批次執行 KILL,避免游標循環開銷。
  • ALTER DATABASE ... SET MULTI_USER 需獨佔資料庫,故必須先清空連線。

進階版(支援新版 DMV)

USE master;
DECLARE @dbname SYSNAME = N'你的資料庫名稱';
DECLARE @kill NVARCHAR(MAX) = N'';

SELECT @kill = STRING_AGG(N'KILL ' + CAST(session_id AS NVARCHAR(10)), N'; ')
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID(@dbname) AND is_user_process = 1;

IF @kill <> N'' EXEC sp_executesql @kill;

ALTER DATABASE @dbname SET MULTI_USER WITH ROLLBACK IMMEDIATE;

註:SQL Server 2017+ 支援 STRING_AGG;舊版可用 FOR XML PATH 串聯。

常見誤區

  • 直接執行 ALTER DATABASE SET MULTI_USER 會報錯:「資料庫正在使用中,無法鎖定」。
  • 使用 sp_who2 手動逐一 KILL,效率低且易漏殺。
  • 忘記切回 MULTI_USER,導致後續應用連線失敗。
copyright © 萬盛學電腦網 all rights reserved