502_6@use master@H_
502_6@go@H_
502_6@declare @spid int,@bl int@H_
502_6@DECLARE s_cur CURSOR FOR@H_
502_6@select 0,blocked@H_
502_6@from (select * from sysprocesses where blocked>0 ) a@H_
502_6@where not exists(select * from (select * from sysprocesses where blocked>0 ) b@H_
502_6@where a.blocked=spid)@H_
502_6@union select spid,blocked from sysprocesses where blocked>0@H_
502_6@OPEN s_cur@H_
502_6@FETCH NEXT FROM s_cur INTO @spid,@bl@H_
502_6@WHILE @@FETCH_STATUS = 0@H_
502_6@begin@H_
502_6@if @spid =0@H_
502_6@select '引起
数据库死锁的是:@H_
502_6@'+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的
sql语法如下'@H_
502_6@else@H_
502_6@select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '@H_
502_6@进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的
sql语法如下'@H_
502_6@DBCC INPUTBUFFER (@bl )@H_
502_6@FETCH NEXT FROM s_cur INTO @spid,@bl@H_
502_6@end@H_
502_6@CLOSE s_cur@H_
502_6@DEALLOCATE s_cur@H_
502_6@
502_6@create proc p_lockinfo@H_
502_6@@kill_lock_spid bit=1,--是否杀掉死锁的进程,1 杀掉,0 仅
显示@H_
502_6@@show_spid_if_nolock bit=1 --如果没有死锁的进程,是否
显示正常进程信息,1
显示,0
不显示@H_
502_6@as@H_
502_6@declare @count int,@s nvarchar(1000),@i int@H_
502_6@select id=identity(int,1,1),标志,@H_
502_6@进程ID=spid,线程ID=kpid,块进程ID=blocked,
数据库ID=dbid,@H_
502_6@
数据库名=db_name(dbid),
用户ID=uid,
用户名=loginame,累计
cpu时间=
cpu,@H_
502_6@登陆时间=login_time,打开事务数=open_tran,进程状态=status,@H_
502_6@工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,@H_
502_6@域名=nt_domain,网卡地址=net_address@H_
502_6@into #t from(@H_
502_6@select 标志='死锁的进程',@H_
502_6@spid,kpid,a.blocked,dbid,uid,loginame,
cpu,login_time,open_tran,@H_
502_6@status,hostname,program_name,hostprocess,nt_domain,net_address,@H_
502_6@s1=a.spid,s2=0@H_
502_6@from master..sysprocesses a join (@H_
502_6@select blocked from master..sysprocesses group by blocked@H_
502_6@)b on a.spid=b.blocked where a.blocked=0@H_
502_6@union all@H_
502_6@select '|_牺牲品_>',blocked,@H_
502_6@s1=blocked,s2=1@H_
502_6@from master..sysprocesses a where blocked<>0@H_
502_6@)a order by s1,s2
select @count=@@rowcount,@i=1
if @count=0 and @show_spid_if_nolock=1@H_502_6@begin@H_502_6@insert #t@H_502_6@select 标志='正常的进程',db_name(dbid),@H_502_6@open_tran,status,net_address@H_502_6@from master..sysprocesses@H_502_6@set @count=@@rowcount@H_502_6@end
if @count>0@H_502_6@begin@H_502_6@create table #t1(id int identity(1,a nvarchar(30),@H_502_6@b Int,EventInfo nvarchar(255))@H_502_6@if @kill_lock_spid=1@H_502_6@begin@H_502_6@declare @spid varchar(10),@标志 varchar(10)@H_502_6@while @i<=@count@H_502_6@begin@H_502_6@select @spid=进程ID,@标志=标志 from #t where id=@i@H_502_6@insert #t1 exec('dbcc inputbuffer('+@spid+')')@H_502_6@if @标志='死锁的进程' exec('kill '+@spid)@H_502_6@set @i=@i+1@H_502_6@end@H_502_6@end@H_502_6@else@H_502_6@while @i<=@count@H_502_6@begin@H_502_6@select @s='dbcc inputbuffer('+cast(进程ID as varchar)+')'@H_502_6@from #t where id=@i@H_502_6@insert #t1 exec(@s)@H_502_6@set @i=@i+1@H_502_6@end@H_502_6@select a.*,进程的sql语句=b.EventInfo@H_502_6@from #t a join #t1 b on a.id=b.id@H_502_6@end@H_502_6@go
exec p_lockinfo@H_502_6@
