-- Kill sessioni NAV dormienti senza transazioni aperte da piu di 20 minuti

USE ER_P

DECLARE @v_spid INT
DECLARE c_Users CURSOR
FAST_FORWARD FOR

SELECT spid
   FROM master..sysprocesses (NOLOCK)
   WHERE spid>50  
   AND (program_name LIKE N'%Microsoft Dynamics NAV%') AND (program_name not like N'%Microsoft Dynamics NAV WEB%')
   AND status='sleeping'
   AND DATEDIFF(mi,last_batch,GETDATE())>20
   AND spid<>@@spid
   AND open_tran = 0

OPEN c_Users
FETCH NEXT FROM c_Users INTO @v_spid
WHILE (@@FETCH_STATUS=0)
BEGIN
  PRINT 'KILLing '+CONVERT(VARCHAR,@v_spid)+'...'
  EXEC('KILL '+@v_spid)
  FETCH NEXT FROM c_Users INTO @v_spid
END

CLOSE c_Users
DEALLOCATE c_Users