SqlServer死锁与阻塞检测脚本

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'sp_Lock_Scan')DROP PROCEDURE sp_Lock_ScanGOCREATE PROCEDURE sp_Lock_ScanASDECLARE @SPID INTDECLARE @BLK INTDECLARE @Count INTDECLARE @Counter INTDECLARE @LOCK BITCREATE TABLE #Temp([Id] INT IDENTITY,[SPID] INT,[BLOCK] INT)SELECT @LOCK = 0IF @@ERROR <> 0 RETURN @@ERRORINSERT INTO #Temp([SPID], [BLOCK])SELECT0, [blocked]FROM(SELECT * FROM [master]..[sysprocesses] WHERE [blocked] > 0) aWHERENOT EXISTS(SELECT * FROM [master]..[sysprocesses] WHERE a.[blocked] = [spid] AND [blocked] > 0)UNIONSELECT [spid], [blocked] FROM [master]..[sysprocesses] WHERE [blocked] > 0IF @@ERROR <> 0 RETURN @@ERRORSELECT @Count = COUNT(*), @Counter = 1 FROM #TempIF @@ERROR <> 0 RETURN @@ERRORIF @Count = 0BEGINSELECT N'没有阻塞和死锁信息' [ScanMessage]RETURN 0ENDELSEBEGINWHILE @Counter <= @CountBEGINIF EXISTS(SELECT * FROM #Temp a WHEREa.[Id] > @CounterANDEXISTS(SELECT * FROM #Temp WHERE [Id] <= @Counter AND a.[BLOCK] = [SPID]))BEGINSELECT @LOCK = 1SELECT @SPID = [SPID], @BLK = [BLOCK] from #Temp WHERE [Id] = @CounterSELECT N'引起数据库死锁的是：【' + CAST(@SPID AS NVARCHAR(255)) + N'】进程,其执行的SQL语言如下' [ScanMessage]SELECT @SPID [SPID], @BLK [BLOCKED]DBCC INPUTBUFFER(@SPID)DBCC INPUTBUFFER(@BLK)ENDSELECT @Counter = @Counter + 1ENDIF @LOCK = 0BEGINSELECT @Counter = 1WHILE @Counter <= @CountBEGINSELECT @SPID = [SPID], @BLK = [BLOCK] FROM #Temp where [Id] = @CounterIF @SPID = 0SELECT N'引起阻塞的是：【'+ CAST(@BLK AS NVARCHAR(255)) + '】，其执行的SQL语法如下' [ScanMessage]ELSESELECT N'进程【' + CAST(@SPID AS NVARCHAR(255)) + N'】被进程【' + CAST(@BLK AS NVARCHAR(255)) + N'】阻塞，当前进程的SQL语法如下' [ScanMessage]DBCC INPUTBUFFER(@SPID)DBCC INPUTBUFFER(@BLK)SELECT @Counter = @Counter + 1ENDENDENDRETURN 0GO