你的位置:首页 > 数据库

[数据库]SqlServer死锁与阻塞检测脚本


 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