Deadlock Solution

ให้ทำการ Run ใน Query Analyzer ไปเรื่อยๆจนกว่าจะไม่เจอ 'Remaining Blockers' และ Victim Count=0

use master
go 
select getdate()
go
-- Report on blocking spids to be terminated.
select blocked as "Blocker" ,count(*) as "Victim Count" 
from master..sysprocesses 
where blocked > 0 
group by blocked
go

DECLARE @blocker varchar(75)
DECLARE blocker_cursor CURSOR FOR select distinct blocked from sysprocesses where blocked !=0

OPEN blocker_cursor
FETCH NEXT FROM blocker_cursor INTO @blocker
WHILE (@@fetch_status <> -1)
BEGIN
   IF (@@fetch_status = -2)
      BEGIN
         FETCH NEXT FROM blocker_cursor INTO @blocker
         CONTINUE
      END

   -- Only kill process if it is the leader and more than 5 seconds old.
   IF @blocker not in (select spid from sysprocesses where blocked != 0) AND (select datediff(ss,last_batch,getdate()) FROM sysprocesses where blocked = @blocker) > 5 
      BEGIN
         print 'killing head blocker, spid #' + @blocker + ' who is blocking connections with this command: '
         print ''
         exec ('dbcc inputbuffer (' + @blocker + ')')
         exec ('kill ' + @blocker ) -- kill
         FETCH NEXT FROM blocker_cursor INTO @blocker

         -- Display Remaining Blocking Spids
         waitfor delay '00:00:03' --wait three seconds for the blocks to clear, then display status
         print ''
         print 'Remaining Blockers...'
         print ''
         select blocked as "Blocker" ,count(*) as "Victim Count" 
         from master..sysprocesses 
         where blocked > 0 
         group by blocked
      END
   ELSE
      FETCH NEXT FROM blocker_cursor INTO @blocker
   END
DEALLOCATE blocker_cursor
print 'Process Complete...'