查询系统中引起死锁的进程及sql语句

use master
go
declare @spid int,@bl int
DECLARE s_cur CURSOR FOR
select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0
OPEN s_cur
FETCH NEXT FROM s_cur INTO @spid,@bl
WHILE @@FETCH_STATUS = 0
begin
if @spid =0
select ' 引起数据库死锁的是 : '+ CAST(@bl AS VARCHAR(10)) + ' 进程号 , 其执行的 SQL 语法如下 '
else
select ' 进程号 SPID ： '+ CAST(@spid AS VARCHAR(10))+ ' 被 ' + ' 进程号 SPID ： '+ CAST(@bl AS VARCHAR(10)) +'
阻塞 , 其当前进程执行的 SQL 语法如下 '
DBCC INPUTBUFFER (@bl )
FETCH NEXT FROM s_cur INTO @spid,@bl
end
CLOSE s_cur
DEALLOCATE s_cur
exec sp_who2
.Net论坛http://www.51pro.net，Asp.net源码http://djcode.taobao.com，贴吧：aspnet