分成為刪除指定資料庫,以及刪除執行個體上的全部連線兩個部分來說明。
以下整理了 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.htmlSQL Server:認識 COALESCE() 函數,補充範例
http://sharedderrick.blogspot.tw/2012/06/t-sql-coalesce_14.html初探Cursors(資料指標) 與資料列集(Rowsets)
http://sharedderrick.blogspot.tw/2013/02/cursors-rowsets.html