You have changed your database to single_user mode to do a task. As you go about your business, you lose track of which connection was the single_user connection. You start closing connections and mistakenly close the session that was your single_user connection. Now you are unable to start a new single_user session. It would seem that somebody or something has taken your connection.
Today, I am going to discuss some things you may do to get around this problem.
The first thing that may come to mind when you encounter this is “Oh crap!” Well, no need to get too terribly worried (not unless you really hosed something up and you are trying to fix it real quick before the boss notices).
The next thing you may think of trying is how to circumvent the single_user mode. And during that thought process you may be thinking that single_user does not really mean single_user so you might try something like start a DAC session. Well, let’s go through that and see what would happen in a DAC session if your single_user session is stolen.
I am going to skip the part of setting a database into single_user mode because we are presuming that the condition already exists. To start a DAC session, I am going to point you to a previous article I did on the topic – here.
To ensure I am using a DAC session, I am going to issue the following query. This will ensure I am in the right session and that DAC is in use.
SELECT s.group_id,e.name, CAST(g.name AS NVARCHAR(20)) AS ResourceGroup, s.session_id
, s.login_time, CAST(s.HOST_NAME AS NVARCHAR(20)) AS HostName
, CAST(s.program_name AS NVARCHAR(20)) AS ProgramName
,s.original_login_name
,s.is_user_process
,s.STATUS
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_resource_governor_workload_groups g
ON g.group_id = s.group_id
INNER JOIN sys.dm_exec_connections ec
ON s.session_id = ec.session_id
LEFT OUTER JOIN sys.endpoints e
ON ec.[endpoint_id]=e.[endpoint_id]
WHERE s.session_id = @@SPID
ORDER BY g.NAME;
GOIn my case, this results in an endpoint with the name “Dedicated Admin Connection” and a spid of 84. Good, I am in the correct session for the rest of this test. Next, I will issue a Use database statement. I have created a test database called ClinicDB. So I will issue the following.
USE ClinicDB;
GOI get the following result.
Msg 924, LEVEL 14, STATE 1, Line 1
DATABASE 'ClinicDB' IS already OPEN and can ONLY have one USER AT a TIME.So, that blows that idea right out of the water. It shouldn’t really have been a consideration in the first place because single_user really means just that – single_user.
Now What?
Well, what do you think we could do now to circumvent this little problem and get that single_user session back?
That requires a little investigative work. It is time to find out who has taken the single_user session and politely ask them to give it up. To make that task a little easier, we could modify the previous query to find out who has that single_user session (thus limiting how many people we have to ask). I have modified the following query to use sys.sysprocesses so I could limit the results to the ClinicDB. This is a limitation of SQL 2008 R2 and older versions. Getting the database reliably means using sysprocesses. Despite the database_id being available in other related DMVs, it’s just not that easy. One would think you could use sys.dm_exec_requests. But if a request is not active, an entry won’t exist for that session. This problem is fixed in SQL 2012 since the sys.dm_exec_connections DMV now has the database_id field. Enough of that birdwalk and on to the query.
SELECT s.group_id,e.name, CAST(g.name AS NVARCHAR(20)) AS ResourceGroup, s.session_id ,DB_NAME(r.dbid) AS DBName
, s.login_time, CAST(s.HOST_NAME AS NVARCHAR(20)) AS HostName
, CAST(s.program_name AS NVARCHAR(20)) AS ProgramName
,s.original_login_name
,s.is_user_process
,s.STATUS
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_resource_governor_workload_groups g
ON g.group_id = s.group_id
INNER JOIN sys.dm_exec_connections ec
ON s.session_id = ec.session_id
INNER JOIN sys.sysprocesses r
ON r.spid = s.session_id
LEFT OUTER JOIN sys.endpoints e
ON ec.[endpoint_id]=e.[endpoint_id]
WHERE DB_NAME(r.dbid) = 'ClinicDB'
ORDER BY g.NAME;
GOI chose not to do an entirely new query to simply demonstrate that it was possible with a very small tweak to what has been already used.
Now that you know (in my case I can see that I have a session open with ID = 80 that is connected to that single_user database), I can walk over to the person (knowing his/her login id and computer name) and politely ask them to disconnect.
In the end, this is really an easy thing to resolve. Sure it may take some people skills – but that doesn’t make the task too terribly difficult. Next time this happens to you, just remember you can run a quick query to find who has sniped that single_user session.
The spawn of SQL server is not so much like the Spawn character of the comics. Nor is it much like the spawn shown to the right.
