* 取消进程的条件:
* 1、锁住了其他进程。 * 2、自身没有被其他进程锁住。 * 3、此进程已经空闲了2分钟以上。 * */ 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; |
|