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;