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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy