Database Stuck in a Single User Mode in SQL Server

Question

Our new DBA has taken the database into a single user mode to perform the detach- attach operation but by mistake he closed the sesion. And ever since then nobody is able to access that database.

I tried to find the session ID by using sp_who2 but it is not showing any session ID on that database.

I am using "SQL Server 2016 Standard Edition" on "Windows server 2016".

score 19 · Accepted Answer · answered Apr 09 '18 at 21:24

By using the the dynamic view "sys.dm_tran_locks" we can find out the active sessions of the database which is stuck in a single user mode as follows:

--Query to find the session_id

SELECT request_session_id FROM sys.dm_tran_locks 

WHERE resource_database_id = DB_ID('YourDatabaseName')

Now kill the session ID found by above query as follows:

-- kill all the processes which are using your database with following query:

    KILL spid

Bring the database into a multi_user mode by using following query:

USE Master
GO
ALTER DATABASE YourDatabaseName SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

Bring the database online.

USE Master
Go
ALTER DATABASE YourDatabaseName SET online
Go

Check the status of the database by using following Query and your Database should be back to normal / multi user mode and online.

select * from sys.databases where name ='YourDatabaseName'

score 8 · Answer 2 · answered Sep 30 '19 at 10:59

Below is the working solution of the problem,

SSMS in general uses several connections to the database behind the scenes.

You will need to kill these connections before changing the access mode.(I have done it with EXEC(@kill); in the code template below.)

Then,

Run the following SQL to set the database in MULTI_USER mode.

USE master
GO
DECLARE @kill varchar(max) = '';
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(10), spid) + '; '
FROM master..sysprocesses 
WHERE spid > 50 AND dbid = DB_ID('<Your_DB_Name>')
EXEC(@kill);

GO
SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE [<Your_DB_Name>] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [<Your_DB_Name>] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

To switch back to Single User mode, you can use:

ALTER DATABASE [<Your_DB_Name>] SET SINGLE_USER

This should work. Happy coding!!

Thanks!!

@MarcinŚmiałek Nice to hear that. It should help other techies as well. — Trilok Pathak, May 24 '23 at 12:42

Database Stuck in a Single User Mode in SQL Server

2 Answers2