DECLARE @P_SPID INT
DECLARE @P_SQL VARCHAR(2000)
--CURSOR PARA OBTENÇÃO DOS PROCESSOS INATIVOS HÁ MAIS DE 20 MINUTOS
DECLARE #CUR_PROCESSOS_INATIVOS CURSOR FOR
SELECT SPID --,PROGRAM_NAME, CMD, LAST_BATCH
FROM MASTER..SYSPROCESSES
WHERE WAITRESOURCE = ''
AND SPID > 50
AND STATUS = 'SLEEPING'
AND DATEDIFF(MI,LAST_BATCH, GETDATE())
> 5
AND WAITTYPE = 0X0000
AND LASTWAITTYPE = 'MISCELLANEOUS'
OPEN #CUR_PROCESSOS_INATIVOS
FETCH NEXT FROM #CUR_PROCESSOS_INATIVOS
INTO @P_SPID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @P_SQL = 'KILL ' +
CONVERT(NVARCHAR(30),@P_SPID)
EXECUTE (@P_SQL)
FETCH NEXT FROM #CUR_PROCESSOS_INATIVOS
INTO @P_SPID
END
CLOSE #CUR_PROCESSOS_INATIVOS
DEALLOCATE #CUR_PROCESSOS_INATIVOS
