SQL deadlocking..in single user mode now

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.


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