SQL KILL INACTIVE NAV SESSIONS

// KILL SQL INACTIVE SESSIONS FOR NAV APPLICATIONS
// ONLY FOR SLEEPING SESSIONS

a very Simple SQL Stored Procedure to 'kill' sql inactive sessions
idle (sleeping) for 10 minutes related to "Nav Client" applications

 *** it's necessary to identify correct "application name" ***
*** ex: program_name LIKE N'%NAV client% *** or %NAV Web%

Stored Procedure Script

DECLARE @v_spid INT
DECLARE c_Users CURSOR
   FAST_FORWARD FOR
   SELECT SPID
   FROM master..sysprocesses (NOLOCK)
   WHERE spid>50
   AND loginame <> 'DIR%'
   AND program_name LIKE N'%NAV client%'
   AND status='sleeping'
   AND DATEDIFF(mi,last_batch,GETDATE())>=10
   AND spid <> @@spid

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