Get Out of SINGLE_USER Mode

less than 1 minute read | Updated:

If you find yourself stuck in SINGLE_USER mode in a SQL Server database, do the following:

1 - Find SPIDs connected to your database

USE master
GO
EXEC sp_who

Note any SPIDs connected to your database, and then…

2 - Kill connections

KILL [spid] -- do this for each spid returned by exec sp_who

3 - Set back to MULTI_USER

USE Master
ALTER DATABASE DatabaseName SET MULTI_USER

Plan B (If that doesn’t work)

If that doesn’t work, the following query can be used to find the connection(s):

SELECT
	request_session_id
FROM
	sys.dm_tran_locks
WHERE
	resource_database_id = DB_ID('DatabaseName')

Note any spid’s that are returned, and then run

KILL [spid] -- do this for each spid returned by the previous query


USE Master
ALTER DATABASE YourDatabase SET MULTI_USER

Source: Stack Overflow

Leave a Comment