FIX: SQL Server Stuck in Single User Mode

If you find the following article useful, consider subscribing to our RSS feeds for more tasty snacks. If you like it, twit this post or follow us. And if you really like it or you'd like to learn more about SummitCloud solutions, please visit our website.

Ever get a SQL Server 2008 database stuck in SINGLE_USER_MODE, then find you can’t maintenance it, access it, and even SP_WHO2 fails?

Maybe you dropped it into SINGLE_USER_MODE to perform a DBCC CHECKDB type of repair, then tried to access it again and voila…you’re not the only one in the database. This could happen in a number of scenarios, but a typical one in a SharePoint environment is the SEARCH database.

Sure, you can stop the service but it might spool up again while you’re performing some cleanup. Sound familiar.

The icing on the cake is that you can’t even query SP_WHO or SP_WHO2 to determine which SPID to kill. The horror!

Following are the steps you can take to:

Determine the SPID you need to kill
Quickly set the Database in question back into MULTI USER MODE

Step by Step:

Use the MASTER DB (because you can’t access the db in question)
Determine which nasty SPID(s) need to be killed
Kill them
Change your DB to MULTI USER mode

Coast

Tried this and it didn't work because you still can't access the database in question. However, sp_who will list the processes up until it can't access the database in question. So I did that and guessed that the next spid must be the one. To verify that, I tried sp_who '<spid>' with the next spid up and it failed with the “single user” message again – voila, I'm golden by killing that spid. Back to normal.</spid>

FIX: SQL Server Stuck in Single User Mode – And You Can’t Get In

About the Author

SummitCloud Solutions