How to exit from single-user mode in SQL Server

How to exit from single-user mode in SQL Server

SQL Server opens multiple connections to serve data to client for their application.

So, you need to kill all opened connections before changing the access mode.

First, make sure the object explorer is pointed to a system database like master.

Second, execute a sp_who2 and find all the connections to database 'my_db'. Kill all the connections by doing KILL { session id } where session id is the SPID listed by sp_who2.

Third, open a new query window.

Then, below script will help to find all current process of sql server and kill and then it will 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

Hope, this will help to access your database.

Enjoy !!!

:)