Single User Mode on SQL Server Management Studio

SamHurd 

Hi, Sam.

Assuming you have the necessary rights on the SQL Server, you can use the following query to determine which login, from which host, under which process is claiming the single connection.

Anecdotally, I tend to find it's something unattended like a Window Service that claims the single connection in these scenarios.

Example

In this example, I'm checking the details on who has the connection to a database named "throwaway".

SELECT
	s.[session_id]
	, s.[login_name]
	, db.[name]
	, s.[host_name]
	, s.[host_process_id]
	, s.[program_name]
FROM
	sys.dm_exec_sessions AS s INNER JOIN
	sys.databases AS db ON
		s.database_id = db.database_id
WHERE
	db.[name] LIKE 'throwaway%';

Example output

In the output we can see who/what is connected, from which host and which program/process they're running that has claimed the single connection.

Once you've kicked the person/process off (i.e. temporarily stopping the Windows Service, sticking with my earlier example), you can then change the mode back to multi_user - assuming nothing else tries connecting after the first claimant has been dealt with (i.e. this could become a circular issue for you).

Cheers,

Lain