SQL SERVER – Deadlock on ALTER DATABASE to MULTI_USER mode

I was trying to alter the database to MULTI_USER mode, but faced this error.

Msg 1205, Level 13, State 68, Line 10 Transaction (Process ID 79) 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 10 ALTER DATABASE statement failed.

Instinctively, you would run sp_who2, and then try to kill the processes connected to that particular database. But what if the SPID is less than 50? You tried to kill it, but you get the the following error.

Msg 6107, Level 14, State 1, Line 1
Only user processes can be killed.

Basically, it says that you cannot kill a system process.

So, to resolve this problem, here is the secret Ninja move. Simply execute the command below.

SET DEADLOCK_PRIORITY HIGH 
ALTER DATABASE YourDBName SET MULTI_USER WITH ROLLBACK IMMEDIATE

Cheers!