SQL: SQL Server database stuck in single user mode

There was a question on Stack Overflow recently about SQL Server being “stuck” in single user mode.

The poster had a SQL Server 2016 instance, running on Windows Server 2016. He said he tried to do this:

  ALTER DATABASE MyDatabase
  SET MULTI_USER;

But it always told him that the database is in use.

The fix

The core concept to understand here is that when a database is in single user mode, you can’t assume that you’re the single user. In this case, he clearly wasn’t.

I’ve seen this happen a lot when people are using SQL Server Management Studio (SSMS) and Object Explorer is connected to the database. It could very well be the single user, not you.

Best advice is to try to find anything that’s connected to the database, and close it. Then try again.

If there’s no luck, restart the SQL Server service, and connect with a tool like SQLCMD that isn’t going to go opening any other connections. Then you should be able to execute the command to put the database back in multi-user mode.

2020-02-20