SqlServer死锁与阻塞检测脚本

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'sp_Lock_Scan')
	DROP PROCEDURE sp_Lock_Scan
GO

CREATE PROCEDURE sp_Lock_Scan
AS
	DECLARE @SPID INT
	DECLARE @BLK INT
	DECLARE @Count INT
	DECLARE @Counter INT
	DECLARE @LOCK BIT

	CREATE TABLE #Temp
	(
		[Id] INT IDENTITY
		,[SPID] INT
		,[BLOCK] INT
	)

	SELECT @LOCK = 0

	IF @@ERROR <> 0 RETURN @@ERROR

	INSERT INTO #Temp
	(
		[SPID], [BLOCK]
	)
	SELECT
		0, [blocked]
	FROM
		(
			SELECT * FROM [master]..[sysprocesses] WHERE [blocked] > 0
		) a
	WHERE
		NOT EXISTS
		(
			SELECT * FROM [master]..[sysprocesses] WHERE a.[blocked] = [spid] AND [blocked] > 0
		)
	UNION
		SELECT [spid], [blocked] FROM [master]..[sysprocesses] WHERE [blocked] > 0

	IF @@ERROR <> 0 RETURN @@ERROR

	SELECT @Count = COUNT(*), @Counter = 1 FROM #Temp

	IF @@ERROR <> 0 RETURN @@ERROR

	IF @Count = 0
		BEGIN
			SELECT N'没有阻塞和死锁信息' [ScanMessage]
			RETURN 0
		END
	ELSE
		BEGIN
			WHILE @Counter <= @Count
				BEGIN
					IF EXISTS
						(
							SELECT * FROM #Temp a 
							WHERE
								a.[Id] > @Counter
							AND
								EXISTS
								(
									SELECT * FROM #Temp WHERE [Id] <= @Counter AND a.[BLOCK] = [SPID]
								)
						)
						BEGIN
							SELECT @LOCK = 1

							SELECT @SPID = [SPID], @BLK = [BLOCK] from #Temp WHERE [Id] = @Counter

							SELECT N'引起数据库死锁的是：【' + CAST(@SPID AS NVARCHAR(255)) + N'】进程,其执行的SQL语言如下' [ScanMessage]

							SELECT @SPID [SPID], @BLK [BLOCKED]

							DBCC INPUTBUFFER(@SPID)
							DBCC INPUTBUFFER(@BLK)
						END
					SELECT @Counter = @Counter + 1
				END

			IF @LOCK = 0
				BEGIN
					SELECT @Counter = 1

					WHILE @Counter <= @Count
						BEGIN
							SELECT @SPID = [SPID], @BLK = [BLOCK] FROM #Temp where [Id] = @Counter

							IF @SPID = 0
								SELECT N'引起阻塞的是：【'+ CAST(@BLK AS NVARCHAR(255)) + '】，其执行的SQL语法如下' [ScanMessage]
							ELSE
								SELECT N'进程【' + CAST(@SPID AS NVARCHAR(255)) + N'】被进程【' + CAST(@BLK AS NVARCHAR(255)) + N'】阻塞，当前进程的SQL语法如下' [ScanMessage]

							DBCC INPUTBUFFER(@SPID)
							DBCC INPUTBUFFER(@BLK)

							SELECT @Counter = @Counter + 1
						END
				END
		END
RETURN 0
GO