Resolved: Database Stuck in Single-User Mode

Today we had an interesting use case where a customer reported that one of the databases they just restored from a backup got stuck in "Single-User" mode in one of their environments.

To resolve it, I first tried running the following command:

ALTER DATABASE MyDB SET MULTI_USER WITH ROLLBACK IMMEDIATE;

In response, I got deadlocked with the dreaded error 1205:

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

"Well, okay then", I figured, and decided to go hard on deadlock priority and transaction isolation level:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET DEADLOCK_PRIORITY HIGH;
GO
ALTER DATABASE MyDB SET MULTI_USER WITH ROLLBACK IMMEDIATE;

But that didn't help. I still got blocked and got error 5064 saying:

Changes to the state or options of database 'MyDB' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

"Well, okay then", I figured, and wrote the code below to force-kill anyone currently connected to the database before trying to change its state:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET DEADLOCK_PRIORITY HIGH;
GO
DECLARE @CMD nvarchar(max) = ''
SELECT @CMD = CONCAT(@CMD, CHAR(10), 'KILL ', session_id)
FROM sys.dm_exec_requests
WHERE database_id = DB_ID('MyDB')

PRINT @CMD
EXEC (@CMD);
GO
ALTER DATABASE MyDB SET MULTI_USER WITH ROLLBACK IMMEDIATE;

But that didn't help either! I still got error 5064 saying:

Changes to the state or options of database 'MyDB' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

How rude!

"Well, okay then", I figured, and wrote the following code that would also run the USE command to capture a session inside the database before changing its state:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET DEADLOCK_PRIORITY HIGH;
GO
DECLARE @CMD nvarchar(max) = ''
SELECT @CMD = CONCAT(@CMD, CHAR(10), 'KILL ', session_id)
FROM sys.dm_exec_requests
WHERE database_id = DB_ID('MyDB')

PRINT @CMD
EXEC (@CMD);
GO
USE MyDB
ALTER DATABASE MyDB SET MULTI_USER;

But I also opened an additional query window, and prepared the following script for execution:

DECLARE @CMD nvarchar(max);

WHILE 1=1
BEGIN
	SET @CMD = NULL;
	SELECT @CMD = ISNULL(@CMD + CHAR(10), '')
			 + CONCAT(N'KILL ', blocking_session_id)
	FROM sys.dm_exec_requests
	WHERE session_id = 1234

	IF @CMD IS NULL BREAK;
	RAISERROR(N'%s',0,1,@CMD) WITH NOWAIT;
	EXEC(@CMD);
END

Instead of "1234" I wrote the actual session ID of the first session. This new script would be responsible for "clearing the way" for the first session, killing any rogue sessions that barge into MyDB and block the first session before it can change the database state.

Kinda like sweeping the way for a Curling rock!

Then, I executed the first session and then immediately executed the second session. That second session killed a few blockers that managed to barge in, and cleared the way for the first session, which successfully changed the database state back to MULTI_USER.

Success! The database is now accessible again!