SINGLE USER mode with deadlock when attempting to ALTER DATABASE to MULTI USER

• jheim

  Say Hey Kid

  Points: 712

  More actions

  October 16, 2015 at 10:18 pm

  #312337

  Copied from SQL2005 topic of the same name....I accidently added it there. I was using sql2012 although applicable to both.

  I originally set my DB to SINGLE USER to zap connections and do an alter on the DB and then alter back to multi-user...all one script....easy....well no.

  I got this error on the final ALTER DATABASE to set multiuser:

  Msg 1205, Level 13, State 68, Line 1

  Transaction (Process ID ##) 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, Line 1

  ALTER DATABASE statement failed.

  5 minutes of panic....attempted all sorts of forms of setting mutli-user with rollback, no_wait, etc....

  Couldn't take DB offline....same deadlock error (because that's also an alter database...). I began to think: "do I need a startup trigger to set to multi-user and restart SQL?"...ugh desperation feeling....

  before that though, I looked at deadlocks in Extended Events default session (XE captures some interesting stuff by default...deadlock info has helped me many times).

  deadlock XML showed spids that were winning the deadlock....most were spid#<50, all TASK MANAGER sessions locking the db on subresource=FULL... I have no idea what those were....still don't.

  I tried to kill them...pretty desperate trying to kill a system spid.....scary. They just respawned. too many of them....like zombies in that movie.

  So I remembered you can set the deadlock priority of a session...SET DEADLOCK PRIORITY # ....looked up range...-10 to 10....so 10 for me! Maybe I can will the war with the zombie hoard with the deadlock priority super power.

  reran the ALTER DATABASE to set multiuser.....now no deadlock error, I won! ...there was a connection that snuck onto the db from an app process so I got the single user error....But I know how to deal with that....killed, it respawned too quick...disabled login of app process....killed it again....it didn't come back...ran my ALTER DATABASE to set multiuser and it worked! re-enabled the login I undercut. and all good.

  In case anyone else has this issue...that SET DEADLOCK PRIORITY for your current session is the trick.

  Side Note: your session can be in the MASTER context...doesn't have to be in your DBs context to run the ALTER DATABASE.....this might seem obvious but I know a DBA (single user mode) who thought he had to have a session in the DBs context to set multiuser...which was vexing because he couldn't get one (since single user and other connection).