找出佔走Single_User Mode資料庫唯一連線的Session ID

找出佔走Single_User Mode資料庫唯一連線的Session ID

當資料庫的狀態被改為Single_User模式後,只容許一個Session登入該資料庫。

萬一該資料庫的唯一連線被占走後,必須找出是哪一個Session佔走連線並Kill該

Session才有機會取得該資料庫唯一連線來連接該資料庫。

下圖中,我們開啟一個Session Id為56的查詢視窗,我們先連線到資料庫DB2,

然後再對DB2資料庫下指令設為Single_User模式。

1

完成上述步驟後,我們用sp_who2來看看目前佔住DB2的Session ID是多少呢?由下圖

可以看見(紅色圈選處),目前佔住DB2唯一的連線Session ID就是56。

2

當我們知道是誰佔住DB2後,就可以如下圖方式,Kill該Session後再用Alter Database

指令將DB2這一個資料庫改回Multi_User。

3

相信朋友們會想說找出是誰佔住Session有甚麼難呢?下圖中我們換一個方式來將

DB2改為Single_User模式。我們一樣用56號Session,然後先連線到master資料庫再

下Alter Database指令(如下圖)。

4

此時我們再用sp_who2來看,會發現56號Session目前是連線到master。然而我們檢視

sysprocesses資料表,看看哪個Session連線在DB2呢?如下圖所示sysprocesses中找不到

任何Session連線在DB2。我也用過sys.dm_exec_sessions等相關DMV來查詢,都是找不

到佔住DB2唯一連線的Session ID。

當我們找不到連線的Session ID,那我們怎麼知道該Kill哪一個Session呢?

5

後來無意間發現可以利用資料庫ACID的重要關鍵,就是Lock。因為任何連線到資料

庫的Session都會在該資料庫物件上要求一個S鎖定。我們就先用sp_lock來看看吧。

如下圖所示,56號的Session在dbid為8的資料庫下了一個S鎖定。

透過這樣的方式,我們就可以順利找出哪個Session佔走唯一的連線,即使是SQL背景

工作佔走連線,也可以利用這種方式找出該Session。

6

下圖只是利用syslockinfo資料表搭配Where條件式搜尋出是否有Session在DB2下S鎖定

,比用sp_lock會更簡單判讀一點。

7

結語 :

也許會有更簡單的方式去找出Session,我也不曉得這樣的方式是不是很精準,提供

給大家參考,如果您有其他的方式,歡迎一起討論。

我是ROCK

rockchang@mails.fju.edu.tw