SQL deadlocking..in single user mode now

Question

Couple of databases produced an error this morning whilst running in Single User Mode. Due to the following error I am unable to do anything :(

Msg 1205, Level 13, State 68, Line 1
Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I receive that error when trying the following (using the Master Database as a Sys Admin):

ALTER DATABASE dbname
SET MULTI_USER;
GO 

For the sake of it I have tried Restarting the SQL Server, I have tried killing any processes and I have even tried resetting the single user myself:

ALTER DATABASE dbname
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

The job which was running was designed to copy the database and put it in single user mode immediately to try and make it faster.

Anyway I can remove the locks?

Answer 1

Had the same problem. This worked for me:

set deadlock_priority high; -- could also try "10" instead of "high" (5)
alter database dbname set multi_user; -- can also add "with rollback immediate"

From ideas/explanation:

http://myadventuresincoding.wordpress.com/2014/03/06...

http://www.sqlservercentral.com/blogs/pearlknows/2014/04/07/...

Answer 2

Ok, I will answer my own.

I had to use the following:

sp_who

which displayed details of the current connected users and sessions, I then remembered about Activity Monitor which shows the same sort of stuff...Anyway that led me away from my desk to some bugger who had maintained connections to the database against my wishes...

Anyway once I had shut the PC down (by unplugging it...deserved it) I could then run the SQL to amend it into MULTI_USER mode (using system admin user):

USE Master
GO

ALTER DATABASE dbname
SET MULTI_USER;
GO

FYI for those who care, this can be used to immediately set the DB to SINGLE_USER:

ALTER DATABASE dbname
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

Further details, if you know the process id you can use kill pid:

kill 62

Bare in mind SSMS creates a process for your user as well, in my case this was being rejected due to another.

EDIT: As Per Bobby's recommendations we can use:

sp_Who2 

This can show us which process is blocked by the other process.