Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Help! I'm stuck in Single User Mode and can't get out!

So, here’s a silly little take on a serious end-user issue where no-one could access the production database.

Got a call from a client recently.  Like something out of a LifeAlert® commercial: “Help! I'm stuck in single user mode and can't get out! “ 

I figured this one was a no-brainer, but it turned out to be a little trickier than expected.

The first thought was to walk the user through using the GUI via SSMS to simply switch back to multi-user. Of course this did not work, and if another process is accessing the database in single-user mode, you wouldn’t be able to access it anyway.  So figured no worries, let's have the user do it via TSQL.

Let’s take a look who or what is in the database.  – Execute sp_who or sp_who2.  Not happening!  So in order to figure out what process is holding up the database and get the SPID, we can execute:

Use Master

GO

Select * from master.sys.sysprocesses

Where spid > 50

            And dbid=DB_ID (‘StuckDB’))  -- replace with your database name

Once you identified the spid to KILL, you can simply execute:

KILL 85 – replace the spid with the one returned (do not use 85 unless it's the actual spid identified)

Then try to bring it back into multi-user role

ALTER DATABASE StuckDb SET MULTI_USER
GO



To further complicate the matter, all these methods resulted in a deadlock. A single-user victim :-O.  This was the message that occurred.

Msg 1205, Level 13, State 68, Server XXXXXXXXXXX, Line 1
Transaction (Process ID 67) was deadlocked on lock resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.
Msg 5069, Level 16, State 1, Server XXXXXXXXXXX, Line 1
ALTER DATABASE statement failed.



Alright, let's try to offline & online the database to clear any transactions and connections.

Database came back online. Still single-user mode, as expected, but thought now we can get it back to multi user. Fail!

Deadlock!

 

Ok, so maybe an open transaction. Let's try our script to get it out of single user mode again, but let's rollback any transactions there using immediate rollback.

 

ALTER DATABASE StuckDB SET MULTI_USER WITH ROLLBACK IMMEDIATE

 

Ha! Said the database, I refuse to COMMIT! No one’s gonna “Alter” me! And single user it still was!


User desperation had them recycle the SQL Server services, but no dice!
This database vowed to stay single!

 

What next?  Well, how long does one wait for commitment.  It’s time to sh#t or get off the pot, or maybe more politely, fish or cut the bait, which means colloquially no more waiting, especially after a period of delay; to either commit to action now!  Don’t you love when we compare databases to real life?  So, let’s use WITH NO_WAIT

 

ALTER DATABASE StuckDB SET MULTI_USER WITH NO_WAIT

 

Just a quick note on these options: WITH ROLLBACK specifies whether to roll back after the specified number of seconds or immediately.  You can use ROLLBACK AFTER [SECONDS] or ROLLBACK IMMEDIATE. 

 

WITH NO_WAIT specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, the request will fail.  A full reference to ALTER DATABASE SET Options can be accessed by clicking on the hyperlink.

 

If you’re using SQL Server 2008 and later, you may use extended events to capture deadlocks and analyze its output.  You can select the xml_deadlock_report event all retrieved from the ringbuffer. The ring buffer target holds event data in memory, and stores tons of useful troubleshooting and performance metadata. Here is one msdn blog on How to monitor deadlock using extended events in SQL Server 2008 and later. 

 

Now what?  We have a single-user mode that can’t commit, and can no longer wait, and checked out the ring buffer target that tells us why we are deadlocked.  We must consider our priorities. 

 

Therefore, if we can set this priority to HIGH, we can lessen the chance that our process gets chosen as the ‘victim’ (Do we really want to say this for folks considering marriage? No, let’s stick to SQL)

 

We can set the deadlock priority by using the t-sql code known as SET DEADLOCK PRIORITY, which specifies the relative importance that the current session continues processing if it is deadlocked with another session.

 

So, if we can ensure that our ALTER Database SET MULTI-USER statement was less likely to deadlock, we may be able to force it to get out of SINGLE USER mode. 

 

SET DEADLOCK_PRIORITY HIGH

 

Now, if we put all this together into a neat little scriptlet, we have

 

USE [master]
SET DEADLOCK_PRIORITY HIGH
exec sp_dboption '[StuckDB] ', 'single user', 'FALSE';
ALTER DATABASE [StuckDB] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [StuckDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE

 

 

Finally, we have our database back to normal multi-user operating mode!  It took a bit to find this information, and hope that this get’s you off the pot, if you’re feeling like a batchelor, uh er, I mean single-user deadlock victim.  Here, there were unyielding app proccesses constantly connecting to the database.  (UPDATE: Another blog, as my esteemed colleage Jason Brimhall (b|t) pointed out, post publication, offers some additional troubleshooting steps on Single-User connections.) 

 

So, I conclude, all the single user databases, if you wanted to find out how to troubleshoot the deadlock, you should’ve put a ring on it! :-O

  


Please follow me on Twitter @Pearlknows


Take our HealthySQL Challenge! Are you SQL Servers healthy? How do you know for sure? Please contact us about our 15-point Health Check report, which will identify areas for improvement, and allow for best practice recommendations for your SQL Server(s). If we find NOTHING wrong with your SQL Server, the report is FREE! Contact us as pearlknows@yahoo.com

 

Comments

Posted by faisalfarouqi on 8 April 2014

Nice stuff!!!

Posted by Jeff Moden on 9 April 2014

How did you get this to work?  The USE statement fails as expected.

Msg 924, Level 14, State 1, Line 1

Database 'JBMTestDB' is already open and can only have one user at a time.

When running the rest of the command, they all fail.  Here's the error messages...

Msg 5064, Level 16, State 1, Line 1

Changes to the state or options of database 'JBMTestDB' 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.

sp_dboption command failed.

Msg 5064, Level 16, State 1, Line 3

Changes to the state or options of database 'JBMTestDB' 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 3

ALTER DATABASE statement failed.

Msg 5064, Level 16, State 1, Line 4

Changes to the state or options of database 'JBMTestDB' 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 4

ALTER DATABASE statement failed.

Posted by RSP on 9 April 2014

Thanks, Jeff - it is supposed to be USE master.  I updated it, and should be refreshed shortly.

As for the Msg 924 , is known, and addressed earlier in the blog where you'll need to KILL the other processes.  Is also addressed in the referenced blog by Jason Brimhall.

Hope this helps!

Posted by Jeff Moden on 9 April 2014

Ah... that's the part I left out.  Thanks, Jason.  I'll give it another try.

Posted by kokila.kondasamy on 10 April 2014

Hi Jeff i was trying to recreate the scenario by setting the database in single user mode through one login and tried to bring the database out of  single user mode through another login.

i did not receive error msg 924 or any deadlock .

the syprocess query for the database returned no record as the connection to the database is not activiy executing any query

the only error msg i recevied are msg 5064 and 5069

setting the deadlock priority and executing alter statement could not able to bring the database out of single user mode

somehow i could not reproduce problem. could you guide where i am wrong.

Posted by RSP on 10 April 2014

These are two different scenarios.  The blog specifically addresses a condition where the database is BOTH in single_user_mode and a deadlock occurrence.  You would need to have a deadlock situation as well. HTH

Posted by DaveO on 10 April 2014

Jeff,

Create article describing what seems like a simple problem at first glance. Really enjoyed your sense of humor. Thanks.

Posted by RSP on 10 April 2014

Why is everyone calling me "Jeff" :-O

Posted by bgdjwoods on 10 April 2014

Robert, Thanks for this article.  It's pretty informative.... I'll keep it in my DR book just in case.... :-)

Posted by enriarg on 25 April 2014

Love it, I had once a database that was getting deadlocks with the ghost clean up process and didn't let us restore, put in single mode or anything.

We pretty much start the service with a trace flag for single user mode.

Leave a Comment

Please register or log in to leave a comment.