TSQL - Deadlock while setting database to MULTI_USER mode

Post date: Mar 25, 2015 12:38:00 PM

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;