Just Code‎ > ‎

TSQL - Deadlock while setting database to MULTI_USER mode

posted Mar 25, 2015, 5:38 AM by Peter Henell   [ updated Mar 25, 2015, 5:42 AM ]
A strange issue when setting the database back to MULTI_USER mode.

Msg 1205, Level 13, State 68, Line 9

Transaction (Process ID 56) 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 9

ALTER DATABASE statement failed.


Someone had snuck in and stolen our single available connection to the database.

This query was used to investigate who it was:
USE Master;
SELECT sd.name, sp.spid, sp.login_time, sp.loginame 
FROM sysprocesses sp 
INNER JOIN sysdatabases sd on sp.dbid = sd.dbid  
WHERE sd.name = 'YourPoorDatabase'
The query showed that was it was me, myself. I had a connection open somewhere.
I went right ahead and forced the database into multi_user mode.

I first try to use the WITH NO_WAIT option because I would prefer to not force a rollback in case my other connection was doing 
something meaningful.
When that did not work I had to force it with the WITH ROLLBACK IMMEDIATE;

The DEADLOCK_PRIORITY HIGH setting will make sure that we will survive the strange deadlock scenario.

The other connection would be picked as the deadlock victim.
USE [master];
SET DEADLOCK_PRIORITY HIGH;
ALTER DATABASE [YourPoorDatabase] SET MULTI_USER WITH NO_WAIT;
ALTER DATABASE [YourPoorDatabase] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
Comments