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