March 10, 2015 at 9:03 am
Recently we had multiple production failures because one database was in single-user mode. I am trying to recreate this scenario to write a script that will kick out that connection that was holding database in single-user.
In SSMS window 1 I run the following code:
use master
go
alter database test
set single_user
with rollback immediate
In window 2 I run this:
select user_access_desc
from sys.databases
where name = 'Test'
Indeed, it shows SINGLE_USER.
Now I want to find out who is connected in order to kill that spid:
select d.name, p.*
from sys.databases d join sys.sysprocesses p
on d.database_id = p.dbid
where d.name = 'Test'
It shows nothing.
I slightly change this query for that specific SPID that's holding database in window 1:
select d.name, p.*
from sys.databases d join sys.sysprocesses p
on d.database_id = p.dbid
where spid = 67 --d.name = 'Test'
Yes, it shows one record, but the database name is master, not test. And indeed, I run it from the context of master. Also tried sp_who, sp_who2, sys.dm_exec_requests - nothing works. For dm_exec_requests it shows nothing because it does not run at the current moment.
Any other solutions to catch this SPID with a script?
Thanks
March 10, 2015 at 10:51 am
There's no need to kick out the connection manually before you can revert to MULTI_USER. You can use ROLLBACK IMMEDIATE:
ALTER DATABASE Whatever SET MULTI_USER WITH ROLLBACK IMMEDIATE
-- Gianluca Sartori
March 10, 2015 at 11:46 am
I tried it, but got the following error:
Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'Test' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
March 10, 2015 at 4:35 pm
I don't have a SQL instance to test right now, but here's a quick thought: try to query sys.dm_tran_locks and see which session is holding a lock on that database.
-- Gianluca Sartori
March 11, 2015 at 9:08 am
spaghettidba (3/10/2015)
I don't have a SQL instance to test right now, but here's a quick thought: try to query sys.dm_tran_locks and see which session is holding a lock on that database.
Thanks Gianluca for a good tip.
Here is the query that I developed:
select DB_NAME(resource_database_id),
*
from sys.dm_tran_locks
where resource_type = 'database'
However, it shows not only my Test databases, but also several others. The only difference that I found between my Test connection and the others is that for column request_owner_type I have 'SESSION' value while the rest connections show 'SHARED_TRANSACTION_WORKSPACE'.
I could not find much info about this, but nevertheless updated my query to this:
select request_session_id
--DB_NAME(resource_database_id),
--*
from sys.dm_tran_locks
where resource_type = 'database'
and request_owner_type = 'SESSION'
It shows correct SPID number. Now I can easily kill it and alter the database.
But what surprised me is that it shows 'S' for request_mode, I expected to be exclusive.
March 11, 2015 at 10:10 am
It does take an exclusive lock, but only while it is actually altering the database.
Once that's completed, it holds the S lock.
Cheers!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply