對這文章發表回應

分成為刪除指定資料庫，以及刪除執行個體上的全部連線兩個部分來說明。

以下整理了 Cursors(資料指標) 版本，以及資料列集(Rowsets)方式來處理。

---

EX1. 刪除指定資料庫上的所有連線

若是要刪除指定資料庫上的所有連線，可以使用以下方式：

ALTER DATABASE 語法的部分引數：

1. SINGLE_USER 引數

指定每次只能有一位使用者存取資料庫。

如果指定了 SINGLE_USER，且沒有其他使用者連接到資料庫，就會封鎖 ALTER DATABASE 陳述式，直到所有使用者都中斷指定的資料庫為止。

資料庫會保留在 SINGLE_USER 模式中，即使是將選項記錄設為關閉的使用者也是如此。
此時其他使用者可以連接到這個資料庫，但只能有一位。

2. WITH ROLLBACK IMMEDIATE 引數

使用終止選項。所有未完成的交易都會回復，與指定資料庫的所有連接都會立即中斷。

若是要刪除指定資料庫上的所有連線，請參考以下的範例：

-- 01_刪除指定資料庫上的所有連線，並變更為單一使用者連線模式
USE master
GO
ALTER DATABASE [資料庫名稱]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
-- 02_切換回多人使用者連線模式
ALTER DATABASE [資料庫名稱]
SET MULTI_USER;
GO

---

EX2. CURSOR 版本，刪除 SQL Server 上全部的連線，但保留本身這條連線不會刪除

-- EX1. CURSOR 版本，刪除 SQL Server 上全部的連線，但保留本身這條連線不會刪除
-- 01_適用 SQL Server 2005、2008、2008 R2、2012 等版本
USE master
GO
CREATE PROC KillUserConnections
AS
DECLARE @spid int, @SQLstr nvarchar(128)
 
DECLARE spids_cr CURSOR FOR
SELECT session_id FROM sys.dm_exec_connections
WHERE session_id<>@@SPID
FOR READ ONLY
 
OPEN spids_cr
 
FETCH spids_cr INTO @spid
 
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @SQLstr = 'KILL ' + CAST(@spid AS varchar)
EXEC sp_executesql @SQLstr
FETCH spids_cr INTO @spid
END
 
CLOSE spids_cr
DEALLOCATE spids_cr
GO
 
-- 檢視 SQL Server 目前所有的連線 session ;所有目前的處理序
SELECT * FROM sys.dm_exec_connections
 
-- 刪除所有的處理序、連線、session
EXEC KillUserConnections
 
-- 再度檢視 SQL Server 目前所有的連線 session ;所有目前的處理序
SELECT * FROM sys.dm_exec_connections
GO

-- 02_以下是 SQL Server 2000 與 2005 通用的版本
USE master
GO
CREATE PROC KillUserConnections
AS
DECLARE @spid int, @SQLstr nvarchar(128)
 
DECLARE spids_cr CURSOR FOR
SELECT spid
FROM master..sysprocesses
WHERE spid<>@@SPID AND net_address<>''
FOR READ ONLY
 
OPEN spids_cr
 
FETCH spids_cr INTO @spid
 
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @SQLstr = 'KILL ' + CAST(@spid AS varchar)
EXEC sp_executesql @SQLstr
FETCH spids_cr INTO @spid
END
 
CLOSE spids_cr
DEALLOCATE spids_cr
 
GO
 
-- 檢視 SQL Server 目前所有的連線 session ;所有目前的處理序
EXEC sp_who2
 
-- 刪除所有的處理序、連線、session
EXEC KillUserConnections
 
-- 再度檢視 SQL Server 目前所有的連線 session ;所有目前的處理序
EXEC sp_who2
GO

其中 sysprocesses 此為 SQL Server 2000 系統資料表。

---

EX3. 資料列集(Rowsets)方式：刪除 SQL Server 上全部的連線，但保留本身這條連線不會刪除

使用資料列集(Rowsets)方式，無須使用 CURSOR 方式。

適用 SQL Server 2005、2008、2008 R2、2012 等版本。

-- EX2. 資料列集(Rowsets)方式：刪除 SQL Server 上全部的連線，但保留本身這條連線不會刪除
-- 01_檢視 SQL Server 目前所有的連線 session ;所有目前的處理序
USE master
GO
SELECT * FROM sys.dm_exec_connections
GO
-- 02_使用 ISNULL() 函數：刪除 SQL Server 上全部的連線，但保留本身這條連線不會刪除
DECLARE @str1 nvarchar(MAX)
SELECT @str1 = ISNULL(@str1, '') + 'KILL ' + CAST(session_id AS nvarchar(10)) + ';'
FROM sys.dm_exec_connections
WHERE session_id<>@@SPID
-- PRINT @str1
EXEC sp_executesql @str1
GO
/*
PRINT 陳述式所回傳的結果：
KILL 52;KILL 51;KILL 53;KILL 54;KILL 56;KILL 57;KILL 58;KILL 59;KILL 60;KILL 61;KILL 62;KILL 63;KILL 64;KILL 65;
*/

-- 03_使用 COALESCE() 函數：刪除 SQL Server 上全部的連線，但保留本身這條連線不會刪除
DECLARE @str1 nvarchar(MAX)
SELECT @str1 = COALESCE(@str1, '') + 'KILL ' + CAST(session_id AS nvarchar(10)) + ';'
FROM sys.dm_exec_connections
WHERE session_id<>@@SPID
-- PRINT @str1
EXEC sp_executesql @str1
GO

若是要改為 SQL Server 2000 版本：

請置換資料表為：master..sysprocesses。

請置換資料行：spid。

---

參考資料


刪除所有連線; Kill All Connection
http://sharedderrick.blogspot.tw/2008/01/kill-all-connection.html

SQL Server：認識 COALESCE() 函數，補充範例
http://sharedderrick.blogspot.tw/2012/06/t-sql-coalesce_14.html

初探Cursors(資料指標) 與資料列集(Rowsets)
http://sharedderrick.blogspot.tw/2013/02/cursors-rowsets.html