November 22, 2016 at 1:28 pm
I had an issue couple of times in production where a process was holding a database in a single-user mode, so I decided to create a job.
I reproduced this problem in a test environment, but still can't find a solution how to catch that SPID.
Here are my steps:
use master
go
create database TestDB
go
In another connection I ran this:
use master
go
alter database TestDB
set single_user
go
select * from
msdb.sys.objects
go
In a new connection:
select name
from sys.databases
where user_access_desc = 'single_user'
Yes, it properly shows that my TestDB in a single-user mode:
name
-------------
TestDB
I am trying to find the holding SPID:
select name, spid
from sys.databases d join sys.sysprocesses p
on d.database_id = p.dbid
where d.name = 'TestDB'
Nothing:
spid name
------ -------------------------------------------
(0 row(s) affected)
Querying these views does not help:
select * from sys.dm_exec_sessions
select * from sys.dm_exec_connections
select * from sys.dm_exec_requests
Next, I wanted to loop through all connections in order to find a text that would mention TestDB. From a connection where I set it to single-user, I noted the SPID number.
dbcc inputbuffer (67)
It showed :
select * from
msdb.sys.objects
And that is right. Being in context of master database I set TestDB to a single-user,and then in another batch in the same connection I select completely unrelated to TestDB query.
Ia there any solution in a such situation?
Thanks
November 22, 2016 at 2:07 pm
just tried your scenario, it looks like an event is put in the SQL Server error log. Try this:
EXEC sp_readerrorlog 0, 1, 'SINGLE_USER'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply