SQL deadlocking..in single user mode now

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/...

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 MasterGOALTER DATABASE dbnameSET MULTI_USER;GO

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

ALTER DATABASE dbnameSET 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.

When related system processes are at dead block scenario.
SPID 15 & SPID 29 - both are background SysProcesses.

This helps in such cases:

--------- START OF CMDs--------   SET DEADLOCK_PRIORITY HIGH ---- could also try "10" instead of "high" (5)  GO  ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE  GO  ALTER DATABASE <dbname> SET MULTI_USER  GO   -------------------------- END OF CMDs ------------------  

Blocking Case with an User DB RESTORE process putting DB in Single_User