SQL Server Database Stuck in Single user Mode

If you have ever had a SQL 2014 or later database stuck in single user mode you will know how frustrating it can be to get it back to multi user.

You might get an error similar to this:

Changes to the state or options of database [yourdatabase] cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

In the situation I recently experienced the database was stuck in single user mode and the reason was that there were services (from an application) that kept trying to connect to it.

So even a restart of the server/service would not help.

In the end, the below script worked. To quickly kill off the connections and then run the command before any of the services managed to connect.

Note: this should only be used in a dev or nonproduction environment. Firstly it kills all connections to the DB then quickly makes it multi-user.

To perform this I was using a DAC connection, however, this may or may not be required. If in doubt use a DAC, otherwise just give it a shot.


USE [master]; 

DECLARE @kill varchar(8000) = ''; SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';' --select * 
FROM sys.dm_exec_sessions WHERE database_id = db_id('[YourDatabase]') 
EXEC(@kill); 
ALTER DATABASE [YourDatabase] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
-----------------

The above may sound easy but when it happened to me and the only online articles talked about the command sp_dboption which is no longer available it was not a quick fix.

Hopefully the code we have provided for you can make it a quick fix.

The reason mine ended up the way it did was because of a restore to a dev environment that went a bit wrong. It is not something that happens often, but next time it does if you bookmark us you will have a pretty quick fix.

Rob StGeorge

Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply