查询Sqlserver数据库死锁的一个存储过程

 1 use master
 2 go
 3 
 4 CREATE procedure sp_who_lock   
 5 as     
 6 begin     
 7    declare @spid int     
 8    declare @blk int     
 9    declare @count int     
10    declare @index int     
11    declare @lock tinyint      
12    set @lock=0      
13  create table #temp_who_lock      
14  (      
15   id int identity(1,1),16   spid int,17   blk int     
18  )  
19      
20  if @@error<>0 return @@error      
21  insert into #temp_who_lock(spid,blk)      
22  select 0,blocked       
23  from (select * from master..sysprocesses where blocked>0)a      
24  where not exists(select * from  master..sysprocesses where a.blocked =spid and blocked>0)      
25  union 
26  select spid,blocked from  master..sysprocesses where blocked>0 
27       
28  if @@error<>0 return @@error      
29  select @count=count(*),@index=1 from #temp_who_lock      
30  if @@error<>0 return @@error      
31  if @count=0      
32  begin     
33   select ‘没有阻塞和死锁信息‘     
34   return 0      
35  end     
36  while @index<=@count      
37  begin     
38   if exists(select 1 from #temp_who_lock a where id>@index and exists(select 1 from #temp_who_lock 
39   where id<[email?protected] and a.blk=spid))      
40   begin     
41    set @lock=1      
42    select @spid=spid,@blk=blk from #temp_who_lock where id=@index     
43    select ‘引起数据库死锁的是: ‘+ CAST(@spid AS VARCHAR(10)) + ‘进程号,其执行的SQL语法如下‘     
44    select  @spid,@blk    
45    dbcc inputbuffer(@spid)      
46    dbcc inputbuffer(@blk)      
47   end     
48   set @[email?protected]+1      
49  end     
50  if @lock=0       
51  begin     
52   set @index=1      
53   while @index<=@count      
54   begin     
55    select @spid=spid,@blk=blk from #temp_who_lock where id=@index     
56    if @spid=0      
57     select ‘引起阻塞的是:‘+cast(@blk as varchar(10))+ ‘进程号,其执行的SQL语法如下‘     
58    else      
59     select ‘进程号SPID：‘+ CAST(@spid AS VARCHAR(10))+ ‘被‘ + ‘进程号SPID：‘+ CAST(@blk AS VARCHAR(10)) 
60            +‘阻塞,其当前进程执行的SQL语法如下‘     
61    dbcc inputbuffer(@spid)    
62    dbcc inputbuffer(@blk)      
63    set @[email?protected]+1      
64   end     
65  end     
66  drop table #temp_who_lock      
67  return 0      
68 end           
69  
70  
71 GO

在查询分析器中执行:

exec sp_who_lock

?

直到最后的结果为：

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!