数据库死锁语句脚本

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

 1 SELECT  L1.resource_type ,
 2         DB_NAME(L1.resource_database_id) AS DatabaseName ,
 3         CASE L1.resource_type
 4           WHEN ‘OBJECT‘
 5           THEN OBJECT_NAME(L1.resource_associated_entity_id,
 6                            L1.resource_database_id)
 7           WHEN ‘DATABASE‘ THEN ‘DATABASE‘
 8           ELSE CASE WHEN L1.resource_database_id = DB_ID()
 9                     THEN ( SELECT   OBJECT_NAME(object_id,
10                                                 L1.resource_database_id)
11                            FROM     sys.partitions
12                            WHERE    hobt_id = L1.resource_associated_entity_id
13                          )
14                     ELSE NULL
15                END
16         END AS ObjectName ,
17         L1.resource_description ,
18         L1.request_session_id ,
19         L1.request_mode ,
20         L1.request_status
21 FROM    sys.dm_tran_locks AS L1
22         JOIN sys.dm_tran_locks AS L2 ON L1.resource_associated_entity_id = L2.resource_associated_entity_id
23 WHERE   L1.request_status <> L2.request_status
24         AND ( L1.resource_description = L2.resource_description
25               OR ( L1.resource_description IS NULL
26                    AND L2.resource_description IS NULL
27                  )
28             )
29 ORDER BY L1.resource_database_id ,
30         L1.resource_associated_entity_id ,
31         L1.request_status ASC;

1 SELECT  W.session_id AS waiting_session_id , 
2         W.waiting_task_address , 
3         W.wait_duration_ms , 
4         W.wait_type , 
5         W.blocking_session_id , 
6         W.resource_description 
7 FROM    sys.dm_os_waiting_tasks AS W 
8 WHERE   W.wait_duration_ms > 5000 
9         AND blocking_session_id IS NOT NULL;

 1 SELECT  request_session_id , 
 2         resource_type , 
 3         DB_NAME(resource_database_id) AS DatabaseName , 
 4         OBJECT_NAME(resource_associated_entity_id) AS TableName , 
 5         request_mode , 
 6         request_type , 
 7         request_status 
 8 FROM    sys.dm_tran_locks AS L 
 9         JOIN sys.all_objects AS A ON L.resource_associated_entity_id = A.object_id 
10 WHERE   request_type = ‘LOCK‘ 
11         AND request_status = ‘GRANT‘ 
12         AND request_mode IN ( ‘X‘, ‘S‘ ) 
13         AND A.type = ‘U‘ 
14         AND resource_type = ‘OBJECT‘ 
15         AND L.resource_database_id = DB_ID();