定时处理死锁

创建一个存储过程,作业里1分种执行一次

 

 

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[Proc_AutoKillLock]    Script Date: 01/31/2012 13:59:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Proc_AutoKillLock]
AS
BEGIN
  SET NOCOUNT ON; 
DECLARE
@USER_ID INT
, @KILLCMD VARCHAR(100)
, @WAIT_TIME INT
-- 设置空闲时间
SET @WAIT_TIME = 2;
-- 根据选择条件，选择出应该取消的进程
DECLARE cur_lock CURSOR FOR
SELECT spid
FROM master..sysprocesses
WHERE
DATEDIFF(minute, last_batch, getdate()) > @WAIT_TIME
AND blocked = 0
AND spid IN
(SELECT blocked
FROM master..sysprocesses);
-- 取消所有选择出的进程。
OPEN cur_lock;
FETCH NEXT FROM cur_lock INTO @USER_ID;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @KILLCMD = 'kill ' + CAST(@USER_ID AS VARCHAR);
EXECUTE (@KILLCMD);
FETCH NEXT FROM cur_lock INTO @USER_ID;
END;
CLOSE cur_lock;
DEALLOCATE cur_lock;
 
  
 
END

 

GO