Working in a development environment, I botched up a restore. After this restore attempt to overwrite my database with the previous version, I had it stuck in single-user mode.

SSMS provided me with helpful messages such as this: Changes to the state or options of database 'PoorDb' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. Additionally, I was told I was the deadlock victim when attempting to set the user mode back to multi-user.

Going forward I looked at several articles from Stack Overflow and various other blogs, and followed the recommended steps such as

set deadlock_priority high
alter database PoorDB set offline with rollback immediate;
alter database PoorDB set multi_user with rollback immediate;

I even added a step to kill the connections to it by using this statement, helpfully posted by Matthew Haugen

-- originally written in help on Stack Overflow 
-- Credit to Matthew Haugen http://goo.gl/0SJYiq

USE master
GO

DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses 
WHERE dbid = db_id('MyDB')

EXEC(@kill);

Finally went through and removed all my connections from master based on an additional post. No luck. Stopped my monitoring tools, no luck. At this point, it felt like a Monday for sure.

Since I was working in a development environment, I went all gung ho and killed every session with my login name, as there seemed to be quite a few , except for the spid executing. Apparently, the blocking process was executing from master, probably the incomplete restore that didn’t successfully rollback. I’ll have to improve my transaction handling on this, as I just ran it straight with no error checks.

VICTORY!

What a waste of time, but at least I know to watch out next time, ensure my actions are checked for error and rolled back. I’m going to just blame it on the darn SSMS GUI. Seems like a convenient scapegoat this time.

Successful pushed out my changes with the following script:

begin try
	print 'restore database [PoorDB]'

	restore database [PoorDB]
	from disk = N'E:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\PoorDB.bak'
	with replace, recovery;

	print 'alter database PoorDB set online with rollback immediate;'
	alter database PoorDB set online with rollback immediate;

	print 'alter database PoorDB set multi_user with rollback immediate;'
	alter database PoorDB set multi_user with rollback immediate;


	print 'SUCCESS: Restored'
end try
begin catch
	select
		error_number()		as [error_number]
		,error_severity()	as [error_severity]
		,error_state()		as [error_state]
		,error_message()	as [error_message]
		,error_line()		as [error_line]
		,error_procedure()	as [error_procedure]


	print 'Error: Set multi_user, and online with rollback immediate'
	alter database PoorDB set multi_user with rollback immediate;
	alter database PoorDB set online with rollback immediate;


end catch