1 IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'sp_Lock_Scan') 2 DROP PROCEDURE sp_Lock_Scan 3 GO 4 5 CREATE PROCEDURE sp_Lock_Scan 6 AS 7 DECLARE @SPID INT 8 DECLARE @BLK INT 9 DECLARE @Count INT 10 DECLARE @Counter INT 11 DECLARE @LOCK BIT 12 13 CREATE TABLE #Temp 14 ( 15 [Id] INT IDENTITY 16 ,[SPID] INT 17 ,[BLOCK] INT 18 ) 19 20 SELECT @LOCK = 0 21 22 IF @@ERROR <> 0 RETURN @@ERROR 23 24 INSERT INTO #Temp 25 ( 26 [SPID], [BLOCK] 27 ) 28 SELECT 29 0, [blocked] 30 FROM 31 ( 32 SELECT * FROM [master]..[sysprocesses] WHERE [blocked] > 0 33 ) a 34 WHERE 35 NOT EXISTS 36 ( 37 SELECT * FROM [master]..[sysprocesses] WHERE a.[blocked] = [spid] AND [blocked] > 0 38 ) 39 UNION 40 SELECT [spid], [blocked] FROM [master]..[sysprocesses] WHERE [blocked] > 0 41 42 IF @@ERROR <> 0 RETURN @@ERROR 43 44 SELECT @Count = COUNT(*), @Counter = 1 FROM #Temp 45 46 IF @@ERROR <> 0 RETURN @@ERROR 47 48 IF @Count = 0 49 BEGIN 50 SELECT N'没有阻塞和死锁信息' [ScanMessage] 51 RETURN 0 52 END 53 ELSE 54 BEGIN 55 WHILE @Counter <= @Count 56 BEGIN 57 IF EXISTS 58 ( 59 SELECT * FROM #Temp a 60 WHERE 61 a.[Id] > @Counter 62 AND 63 EXISTS 64 ( 65 SELECT * FROM #Temp WHERE [Id] <= @Counter AND a.[BLOCK] = [SPID] 66 ) 67 ) 68 BEGIN 69 SELECT @LOCK = 1 70 71 SELECT @SPID = [SPID], @BLK = [BLOCK] from #Temp WHERE [Id] = @Counter 72 73 SELECT N'引起数据库死锁的是：【' + CAST(@SPID AS NVARCHAR(255)) + N'】进程,其执行的SQL语言如下' [ScanMessage] 74 75 SELECT @SPID [SPID], @BLK [BLOCKED] 76 77 DBCC INPUTBUFFER(@SPID) 78 DBCC INPUTBUFFER(@BLK) 79 END 80 SELECT @Counter = @Counter + 1 81 END 82 83 IF @LOCK = 0 84 BEGIN 85 SELECT @Counter = 1 86 87 WHILE @Counter <= @Count 88 BEGIN 89 SELECT @SPID = [SPID], @BLK = [BLOCK] FROM #Temp where [Id] = @Counter 90 91 IF @SPID = 0 92 SELECT N'引起阻塞的是：【'+ CAST(@BLK AS NVARCHAR(255)) + '】，其执行的SQL语法如下' [ScanMessage] 93 ELSE 94 SELECT N'进程【' + CAST(@SPID AS NVARCHAR(255)) + N'】被进程【' + CAST(@BLK AS NVARCHAR(255)) + N'】阻塞，当前进程的SQL语法如下' [ScanMessage] 95 96 DBCC INPUTBUFFER(@SPID) 97 DBCC INPUTBUFFER(@BLK) 98 99 SELECT @Counter = @Counter + 1100 END101 END102 END103 RETURN 0104 GO
原标题：SqlServer死锁与阻塞检测脚本
关键词：sql
我知道了 