茫茫網海中的冷日
         
茫茫網海中的冷日
發生過的事,不可能遺忘,只是想不起來而已!
 恭喜您是本站第 1664629 位訪客!  登入  | 註冊
主選單

Google 自訂搜尋

Goole 廣告

隨機相片
F09_542.jpg

授權條款

使用者登入
使用者名稱:

密碼:


忘了密碼?

現在就註冊!

DB研討會 : [轉貼]MS-SQL 刪除所有連線(Kill All Connection)

發表者 討論內容
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15766
[轉貼]MS-SQL 刪除所有連線(Kill All Connection)

刪除所有連線(Kill All Connection),資料庫層級、執行個體層級。以 Cursors(資料指標) 與資料列集(Rowsets) 方式為例。


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

以下整理了 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



原文出處:德瑞克:SQL Server 學習筆記: 刪除所有連線(Kill All Connection),資料庫層級、執行個體層級。以 Cursors(資料指標) 與資料列集(Rowsets) 方式為例。
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15766
[轉貼]MS SQL Server 獨佔連線後踢掉的方法

SQL Server獨佔連線後踢掉的方法

在SQL Server要修改DB參數或復原時,常常會因為DB有其他連線使用而出現
「資料庫無法獨佔鎖定來執行此作業。」的錯誤訊息
為此必須使用 SET SINGLE_USER的指令來踢掉其他連線再處理
不過最近遇到踢掉他人後連線卡在上面,任何操作都表示連線已被人獨占而無法執行
遇到這種情形,雖然可能可以用重啟SQL Server解決
但實務上Server上有其他的DB正在運作,怎可能隨便讓你重開
不用擔心,SQL Server是可以把目前連線給踢掉的...只要你有sa權限

以下假設要操作的DB叫做MyDB

--踢掉其他連線的語法
USE master;
GO
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

--查詢目前連線的語法
USE master;
GO
exec sp_who

--找到目前卡住的連線(可看dbname),記住spid再踢掉
--下方的(spid)請改成數字,如80
kill (spid)

--還原成多人使用模式的語法
ALTER DATABASE MyDB SET MULTI_USER;
GO


順便記錄我最近遇過的問題,免得未來忘記
SQL Server預設的定序(Collation)是「 大小寫不分」(Chinese_Taiwan_Stroke_CI_AS)
也就是如果有資料叫做"ADMIN",用"admin"比對也會找到
但存入的時候是大小寫區分("ADMIN"跟"admin"兩筆資料可同時存在)
這會造成存入資料時若不先卡大小寫比對
就會有可產生Admin/admin等大小寫不同的帳號但比對時有誤的情形
(你說查詢的時候加入COLLATE關鍵字?Linq做不到啊)

原本我以為可以靠改DB的定序解決
沒想到原本定序為<資料庫預設值>的參數,修改DB定序後不會跟著改變
仍然是Chinese_Taiwan_Stroke_CI_AS
要改的話可能得針對指定欄位修改
但我也不希望一個DB有多種定序,太雷了
總之如果希望DB預設是大小寫區分的話,請記得修改定序為Chinese_Taiwan_Stroke_CS_AS

--查看DB定序
SELECT DATABASEPROPERTYEX('MyDB', 'Collation')
GO

--查看DB內所有欄位的定序
USE MyDB
GO
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U')

使用SQL Command修改DB定序的語法如下
要注意如果DB有用到索引,可能要先把索引移除,不然無法修改定序

USE master;

GO
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE MyDB
COLLATE Chinese_Taiwan_Stroke_CS_AS;
GO

ALTER DATABASE MyDB SET MULTI_USER;
GO

最後提一個網路上已有很漂亮解法的問題
SQL Server設定某欄為唯一約束(Unique constraint)的情況下
如果該欄允許NULL,裡面的值也只能存在一個NULL
(例如NULL/NULL/1/2/3,這樣不允許,NULL/1/2/3才允許)

有沒有辦法設定某欄位的值在非NULL的情況下是唯一?
SQL 2005之前沒辦法
SQL 2008開始可以對索引下篩選運算式,這個問題就變得很簡單了( 來源

CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;

要移除的話,可以用Drop
DROP INDEX [YourTable].idx_yourcolumn_notnull



原文出處:SQL Server獨佔連線後踢掉的方法 @ 正因為活著 :: 痞客邦 ::
前一個主題 | 下一個主題 | 頁首 | | |



Powered by XOOPS 2.0 © 2001-2008 The XOOPS Project|