SQL 查看死锁情况的存储过程

SQL 查看死锁情况的存储过程

1. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_who_lock]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)    
2.   drop procedure [dbo].[sp_who_lock]    
3.   GO    
4.   /***************************************************************************    
5.   // 创建 :    
6.   // 日期 :  
7.   // 修改 :     
8.   // 说明 : 查看数据库里阻塞和死锁情况    
9.   ***************************************************************************/    
10.   use master    
11.   go    
12.   create procedure sp_who_lock    
13.   as    
14.   begin    
15.   declare @spid int,@bl int,    
16.   @intTransactionCountOnEntry int,    
17.   @intRowcount int,    
18.   @intCountProperties int,    
19.   @intCounter int    
20.      
21.   create table #tmp_lock_who (    
22.   id int identity(1,1),    
23.   spid smallint,    
24.   bl smallint)    
25.      
26.   IF @@ERROR<>0 RETURN @@ERROR    
27.      
28.   insert into #tmp_lock_who(spid,bl) select 0 ,blocked    
29.   from (select * from sysprocesses where blocked>0 ) a    
30.   where not exists(select * from (select * from sysprocesses where blocked>0 ) b    
31.   where a.blocked=spid)    
32.   union select spid,blocked from sysprocesses where blocked>0    
33.      
34.   IF @@ERROR<>0 RETURN @@ERROR    
35.      
36.   -- 找到临时表的记录数     
37.   select @intCountProperties = Count(*),@intCounter = 1    
38.   from #tmp_lock_who    
39.      
40.   IF @@ERROR<>0 RETURN @@ERROR    
41.      
42.   if @intCountProperties=0    
43.   select '现在没有阻塞和死锁信息' as message    
44.      
45.   -- 循环开始     
46.   while @intCounter <= @intCountProperties    
47.   begin    
48.   -- 取第一条记录     
49.   select @spid = spid,@bl = bl    
50.   from #tmp_lock_who where Id = @intCounter    
51.   begin    
52.   if @spid =0    
53.   select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'    
54.   else    
55.   select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'    
56.   DBCC INPUTBUFFER (@bl )    
57.   end    
58.      
59.   -- 循环指针下移     
60.   set @intCounter = @intCounter + 1    
61.   end    
62.      
63.      
64.   drop table #tmp_lock_who    
65.      
66.   return 0    
67.   end