sqlserver自动杀死锁

sqlserver 自动杀死锁

GO

SELECT * FROM master.dbo.sysdatabases WHERE name = 'posserver'

GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'Up_AutoKillBlocked')
DROP PROCEDURE Up_AutoKillBlocked

GO

CREATE PROCEDURE Up_AutoKillBlocked
WITH ENCRYPTION
AS
BEGIN

	DECLARE @blockid smallint
	DECLARE @proc_name varchar(200)
	
	
	SET @blockid = 0
	
	WHILE 1 > 0
	BEGIN
		WHILE EXISTS(SELECT blocked FROM master.dbo.sysprocesses WHERE  blocked > 0 AND dbid = 100)
		BEGIN
			SELECT TOP 1 @blockid = blocked FROM master.dbo.sysprocesses WHERE  blocked > 0 AND dbid = 100
	
			IF ISNULL(@blockid,0) > 0 
			BEGIN
				SET @proc_name = 'KILL ' + CONVERT(VARCHAR(10), @blockid)
				EXEC @proc_name
			END
			WAITFOR DELAY '00:00:01'
		END
	
		WAITFOR DELAY '00:00:05'
	END
	
END

GO

EXEC Up_AutoKillBlocked

GO