I got call from one of my client when I was about to leave office and they said their missing critical databases is not coming into multi_user mode.

I was like it’s going to be very easy to resolve.

I tried to run

Alter database <dbname> with multi_user and got below error.

Msg 1205, Level 13, State 68, Server XXXXXXXXXXX, Line 1
Transaction (Process ID 108) 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.

I tried to run –

Alter database <dbname> with multi_user with rollback immediate but again same error.

I looked to extended event for deadlock and it was showing nothing.

I looked into processes running on server and found deadlock is happening on system processes, so I can’t even KILL those processes.

Meanwhile client said they have bring all the application down but again no luck.

Finally, After doing some research I am able to find the solution and the solution is set the priority of your session high by below t-sql and it will allow you to run your query.

SET DEADLOCK_PRIORITY HIGH

go

Alter database <dbname> set multi_user

You can find more information here –

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-deadlock-priority-transact-sql

Note – This work  around also help you to resolve any deadlock issue in short run.