Unlocking a Deadlocked SQL Database

When you have a deadlocked database, you need to first find the process that has deadlocked the system. The code below will tell you what process that is, just replace the ‘xspoc’ for the database name if its something other than xspoc.

SELECT sd.[name], sp.spid, sp.login_time, sp.loginame 
FROM sysprocesses sp 
INNER JOIN sysdatabases sd on sp.dbid = sd.dbid 
WHERE sd.[name] = 'xspoc'

Once you have your process number, insert it into the script you see below. For this example I used 56, just be sure to include the number you received fromthe code above.

KILL 56
GO
SET DEADLOCK_PRIORITY HIGH
GO
ALTER DATABASE xspoc SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO