DECLARE

@USER_ID INT, @KILLCMD VARCHAR(100)

-- 根据选择条件，选择出应该取消的进程

DECLARE cur_lock CURSOR FOR

SELECT spid FROM master..sysprocesses

WHERE

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;

ITPub博客

【sqlserver】杀掉所有死锁进程