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"
Ok, I will answer my own.
I had to use the following:
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
ALTER DATABASE dbnameSET SINGLE_USER WITH ROLLBACK IMMEDIATE;GO
Further details, if you know the process id you can use
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:
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 ------------------