How to get database out of single user OR How to Drop Database in Single_User Mode OR Set a Database to Single-user Mode OR Set database from SINGLE USER mode to MULTI USER OR Remove SQL Server database from single-user mode

USE MASTER;

GO

ALTER DATABASE [DatabaseName] SET MULTI_USER

OR

ALTER DATABASE [DatabaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATE

OR

EXEC SP_DBOPTION '[DatabaseName]', 'SINGLE USER', 'FALSE';

Below error occurred:-

Msg 5064, Level 16, State 1, Line 1 Changes to the state or options of database 'DatabaseName' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

Use the below Query to find all the database sessions connected to this database.

SELECT D.NAME, D.DBID, SPID, LOGIN_TIME, NT_DOMAIN, NT_USERNAME, LOGINAME

FROM SYSPROCESSES P

INNER JOIN SYSDATABASES D ON P.DBID = D.DBID

WHERE D.NAME =  '[DatabaseName]'

OR

SELECT D.NAME, D.DBID, 'KILL ' + CAST(SPID AS VARCHAR), LOGIN_TIME, NT_DOMAIN, NT_USERNAME, LOGINAME

FROM SYSPROCESSES P

INNER JOIN SYSDATABASES D ON P.DBID = D.DBID

WHERE D.NAME =  '[DatabaseName]'

'KILL ' + CAST(SPID AS VARCHAR) used for concat the SPID with Kill command

Kill the particular session(s) with the Kill command.

Kill [SPID]

Example. Kill 51

Now set database from SINGLE USER mode to MULTI USER

Using SQL Server Management Studio

To set a database to single-user mode or vice-verse

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Right-click the database to change, and then click Properties.
3. In the Database Properties dialog box, click the Options page.
4. From the Restrict Access option, select Single.
5. If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.

Using SQL Server Query

USE MASTER;

GO

ALTER DATABASE [DatabaseName] SET MULTI_USER

OR

ALTER DATABASE [DatabaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATE

OR

EXEC SP_DBOPTION '[DatabaseName]', 'SINGLE USER', 'FALSE';