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