SQL Server database stuck in SINGLE_USER mode

If you have a database stuck in SINGLE_USER mode, where it is not possible to set it to MULTI_USER because another process has excluding use to it, there are a couple of methods to try which will regain access to it.

This is the command which is usually executed to change the database from SINGLE_USER mode to MULTI_USER mode:

ALTER DATABASE YourDBName

SET MULTI_USER;

GO

And the message after trying to execute this command:

Changes to the state or options of database 'YourDBName' cannot be made at this time.

The database is in single-user mode, and a user is currently connected to it.

You can try running:

EXEC sp_who

Which will show the process ID connected to the database, then you can run:

KILL <SPID>

Where <SPID> is the SPID of the process, however, if an SPID doesn’t show, you can also use the following method.

In SQL Server Management Studio, right-click the database, then select Tasks > Take Offline.

Select to ‘Drop All Active Connections’.

Now run the following command:

ALTER DATABASE YourDBName

SET MULTI_USER

WITH ROLLBACK IMMEDIATE

GO

After setting the database to MULTI_USER, you can now bring the database back online.