This is what I found out lately: “Sometimes, brute force is the best way to get something done.”
Let’s say you have a production server and you were in the middle of a detach process, when the detach failed after the database went into single-user mode. In my situation somehow I ended up with a parallel deadlock (where process “X” is blocked by “Y” which in turn is blocked by “X”).
So I tried to use ALTER DATABASE TestDB SET MULTI_USER to bring it back into multi user mode, but I was getting the following error:
Error 1205: Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
So my first though was to set DEADLOCK_PRIORITY to HIGH and try it. The logic is that since deadlock priority can be set by the user I wanted to tell the SQL Server to allow my process to continue and drop the other process. So I tried:
USE master; GO SET DEADLOCK_PRIORITY HIGH; GO ALTER DATABASE TestDB SET MULTI_USER WITH NO_WAIT;
This failed too and I figured it made sense since you would always get a deadlock between the ALTER DATABASE session and the application’s session already exclusively using the database. So basically, the ALTER DATABASE session was chosen as deadlock victim since generally the transaction that requires the least amount of overhead to rollback is the transaction that is aborted!
So now What?!? That was when I found this thread online that talks about the brute force method when all your niceness is not getting you anywhere!
The idea is pretty simple, and not so much elegant; but hey, you can’t complain when it works! You are basically running the line below a 1000 times and you are hoping that during one of these tries SQL server realizes that you are not giving up and it might be cheaper on its resources to set your session as the exclusive session.
USE TestDB; GO 1000
It took it about 300 tries before SQL Server gave up and gave me the exclusive session, from which I ran the ALTER DATABASE script to set the DB back to multi-user mode.