SQL SERVER查找死锁

sql 李磊 804浏览

操作数据库,经常会遇到死锁的情况,有点时候,不得不重启数据库!其实查询出死锁,直接kill,就OK了!代码如下:

declare @spid int,@blk int
DECLARE cur CURSOR FOR
select 0 ,blocked
from (select * FROM master.sys.sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sys.sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sys.sysprocesses where blocked>0
OPEN cur
FETCH NEXT FROM cur INTO @spid,@blk
WHILE @@FETCH_STATUS = 0
begin
      if @spid =0
             select ‘ 引起死锁的进程号是 : ‘+ CAST(@blk AS VARCHAR(10)) + ‘, 其执行的 SQL 语法如下 ‘
     else
            select ‘ 进程号 SPID : ‘+ CAST(@spid AS VARCHAR(10))+ ‘被 ‘ + ‘ 进程号 SPID : ‘+ CAST(@blk AS VARCHAR(10)) +’阻塞 , 其当前进程执行的 SQL 语法如下 ‘
DBCC INPUTBUFFER (@blk )
FETCH NEXT FROM cur INTO @spid,@blk
END
CLOSE cur
DEALLOCATE cur

转载请注明:一笑而过 » SQL SERVER查找死锁

喜欢 (6)or分享 (0)