October 16, 2015 at 10:13 pm
I realize there is at least one other thread on SQLSERVER CENTRAL about this topic....but it was 2011 and someone told me once its bad form to comment on old threads. So forgive me if this is bad form ...I just wanted something out there in case someone is googling on this issue.
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).
October 16, 2015 at 10:17 pm
this was supposed to be in SQL2012...I inadvertently added to sql2005 section...will create a new one there as well.
October 19, 2015 at 1:38 am
All replies to this thread please
http://www.sqlservercentral.com/Forums/Topic1728702-2799-1.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply