/* 
**  Sccsid = "@(#)sp__idlereaper	1.6 03/11/05"
**
**  sp__idlereaper - Created Feb 2005. Original author info and comments follow. 
**
**
**	This stored procedure runs in the background and kills idle
**	user connections.
**
**	Usage:
**	sp_idleReaper <max idle minutes> <run interval minutes> [<verbose>]
**
**		max idle minutes specifies how long a connection should be 
**			allowed to remain idle before being killed.
**		run interval minutes specifies how often we should check idle
**			processes.
**
**	Example: kill tasks idle for 30 minutes, checking every 5 minutes:
**
**			sp_idleReaper 30, 5
**
**	Requires: ASE version 12.5.0.3 or higher with MDA tables setup and
**		monitoring enabled.  Specifically, the "enable monitoring" and
**		"process wait events" configuration parameters must be set to 1.
**
**	History:
**		David Wein			August, 2003	(Written)
**
**		Rob Verschoor (rob@sypron.nl)	24-Sep-2003
**              Added some logging statements (marked 'RobV')
**                     
**          Anon                   March 2005
**              Fixed waitfor bug, already running check, version info, 
**              enhanced logging and minor restructuring.
**
*/

use sybsystemprocs
go

if exists (select * from sysobjects where name = "sp__idlereaper")
begin
	drop procedure sp__idlereaper
end
go

create procedure sp__idlereaper
   @maxIdle int,	/* max idle time in minutes before process is killed */
   @runInterval int,	/* interval, in minutes, between idle process checks */	
   @version varchar(5) = Null
As

Declare @runInterval_HR	int,		/* hours between runs */
        @runInterval_MN	int,		/* minutes between runs */
        @run_spid	smallint,
        @waitevent 	smallint,	/* receive sleep wait event id */
        @kill_spid	smallint,	/* spid to kill */
        @idle_mins      varchar(20),	/* formatted idle for prints */
        @interval_mins  varchar(20),	/* formatted interval for prints */
        @kill_string 	varchar(20),	/* string to execute kill */
        @runInterval_waitfor varchar(30),	/* waitfor string */
        @version_info   varchar(255),
        @logmsg varchar(150)            /* for logging */



If (proc_role("sa_role") < 1)
Begin
   Print "Must have sa_role to run this proc "
   Return 1
End


/* Set the version. Print & exit if requested */
Select @version_info = "@(#)sp__idlereaper	1.6 03/11/05"
If @version Is Not Null
Begin
   Print "Version: %1!", @version_info
   Return 2
End


/** Check that this is not already running. Check the clientname and make
*** sure it is running as sa. Without sa check, anyone could set clientname
*** and defeat this system by hitting this check.
**/
Select @run_spid = spid From master..sysprocesses
Where suid = suser_id("sa") And clientname = "sp__idlereaper" And spid != @@spid
If @run_spid Is Not Null
Begin
   Set Background On
   Print "Error. Proc sp__idlereaper is already running with spid: %1!", @run_spid
   Set Background Off
   Return 3
End


/* RobV 23-Sep-2003: identify ourselves... */
set clientname sp__idlereaper
 

/** build the char values for prints */
Select @idle_mins = convert(varchar,@maxIdle),
       @interval_mins=convert(varchar,@runInterval)

/* RobV 23-Sep-2003: write notification to ASE errorlog */
Set Background On
print "Starting sp__idlereaper. Idle: %1! mins; Interval: %2! mins ...",
      @idle_mins, @interval_mins
set background off


/*
** This procedure uses waitfor delay to implement an
** interval for check idle processes.  The next few 
** lines take the @runInterval parameter and turn it
** into a string that can be passed to execute to 
** initiate the waitfor.
*/

/* how many hours and minutes will I wait? */
select @runInterval_HR = @runInterval / 60, 
       @runInterval_MN = @runInterval % 60
	
/* build the waitfor string */
select @runInterval_waitfor = "waitfor delay '" + 
	convert(varchar(2),@runInterval_HR) + ":" + 
	convert(varchar(2), @runInterval_MN) + ":00'"

/* 
** The WaitTime field in monProcessWaits represents
** miliseconds of wait time.  Therefore, we must convert 
** @maxIdle from minutes to miliseconds 
*/
select @maxIdle = @maxIdle * 60000


/* 
** This procedure uses three temp tables, which we will
** now create.
**
** #sleepTracker keeps track of sleeping tasks between runs
** of the loop.  This is our store of Waits and WaitTime that
** allows us to determine if a task should be killed.
**
** #idleProcess holds tasks that are idle for a given run of
** of the loop.  This are tasks that are idle at this time.  This
** table is truncated at the end of the loop.
**
** #killThese is a list of tasks that qualify to be killed.
** A cursor scans through this list and issues a kill on each task.
** This table is truncated at the end of the loop.
*/

/* create the #sleepTracker table */
select SPID, Waits, WaitTime
into #sleepTracker
from master.dbo.monProcessWaits
where 1 = 2

create unique clustered index sleepTracker_CI on #sleepTracker(SPID)

/* create the #idleProcess table */
select * into #idleProcess from #sleepTracker

/* create the #killThese table */
create table #killThese (SPID smallint)

/* declare a cursor for processing the killable spids */
declare kill_crsr cursor for
select SPID from #killThese

/* determine the proper wait event id */
select @waitevent = WaitEventID 
from master.dbo.monWaitEventInfo
where Description = "waiting for incoming network data"

/*
** Enter a "forever" loop.  Each time through the loop we compare
** currently idle tasks to those that we have stored in the #sleepTracker
** table.  Tasks which have been idle in excess of #maxIdle are killed.
** This procedure "yields" at the end of the loop by called waitfor
** delay based on the @runInterval parameter.
*/
while(1=1)
begin
		
   /* 
   ** There are 7 basic steps to this procedure:
   **	1. detemine which tasks are currently idle
   **	2. remove any tasks from our tracking table that 
   **		are no longer idle.
   **	3. generate a list of tasks that should be killed.
   **	4. update our tracking table for any idle tasks
   **		that did some work between runs of this loop
   **	5. add any newly idle tasks to our tracking table
   **	6. kill the eligable tasks
   **	7. cleanup and get ready to do it again
   */

   /* Step 1: Find idle USER processes and populate the #idleProcess table */
   insert #idleProcess 
   	select p.SPID, pw.Waits, pw.WaitTime
   	from master.dbo.monProcess p,
   	master.dbo.monProcessWaits pw
   	where p.Command = "AWAITING COMMAND" And
   	      p.SPID = pw.SPID And
   	      pw.WaitEventID = @waitevent


   /* 
   ** Step 2: Remove any process from our tracking table 
   ** that isn't still idle.
   */
   delete #sleepTracker 
   where SPID not in 
   	(select st.SPID 
   	from #sleepTracker st,
   	#idleProcess ip
   	where st.SPID = ip.SPID)

   /* 
   ** Step 3: Get a list of spids that must be killed.  The criteria is:
   **	1. SPID must exist in #sleepTracker and #idleProcess.
   **	2. The number of waits my be identical between the two tables.
   **	3. The diff. in WaitTime must be >= the specified @waittime.
   */
   insert #killThese 
   select st.SPID 
   	from #sleepTracker st, #idleProcess ip
   	where st.SPID = ip.SPID
     	      and st.Waits = ip.Waits
   	      and (ip.WaitTime - st.WaitTime) >= @maxIdle

   /* 
   ** Step 4: A process listed in sleepTracker may appear in idleProcess
   ** but did some useful work in-between runs.  In that case the 
   ** Waits field will be incremented.  Update #sleepTracker for
   ** anybody that is in a new wait.
   */
   update #sleepTracker 
   	set Waits = ip.Waits, 
   	WaitTime = ip.WaitTime
   	from #sleepTracker st,
   	#idleProcess ip
   	where st.SPID = ip.SPID
              and ip.Waits > st.Waits

   /* Step 5: Insert into #sleepTracker any newly idle processes */
   insert #sleepTracker 
   	select * from #idleProcess ip
   	where ip.SPID not in
   		(select st.SPID 
   		from #sleepTracker st,
   		#idleProcess ip
   		where st.SPID = ip.SPID)
   	
   /* 
   ** Step 6: process the list of killable spids.  We do this by 
   ** running though the previously declared kill_crsr.  
   */
   open kill_crsr
   fetch kill_crsr into @kill_spid

   while (@@sqlstatus = 0)
   begin

      /* build the kill string */
      select @kill_string = "kill " + convert(varchar(15), @kill_spid)

      /* 
      ** Be careful!  There is a chance that the task was idle when we
      ** queried monProcess but is now doing useful work.  In that case
      ** we just want to leave the guy alone.  There is another possible
      ** race condition where that we do not guard against.  It is 
      ** possible that the task we are about to kill did some work
      ** after we queried monProcess but is now idle again.  This case
      ** is not tested for in this stored procedure.
      */
      if exists (select SPID from master.dbo.monProcess 
                	where SPID = @kill_spid and 
                              Command = "AWAITING COMMAND")
      begin
   	/* log killed process */
         select @logmsg = "sp__idlereaper. Killing spid " + 
                   convert(varchar,@kill_spid) + 
                   " (" + suser_name(suid) + ", " +
                   Case
                      When (hostname Is Null Or hostname = '') Then ipaddr
                      Else rtrim(hostname) + "/" + ipaddr
                   End + ", " + 
                   convert(varchar,loggedindatetime,1) + " " +
                   convert(varchar,loggedindatetime,8) +
   	           "): idle over " + @idle_mins + " mins" 
   	 from master.dbo.sysprocesses where spid = @kill_spid


   	 Set Background On
    	 print @logmsg   /* write message to errorlog */
   	 set background off

   	 /* finally kill the spid and log a message */
   	 execute (@kill_string)
      end

      /* get the next one */
      fetch kill_crsr into @kill_spid
   end    /* while sqlstatus = 0 */
		
   close kill_crsr
		
   
   /* 
   ** Step 7: cleanup and get ready to do it again.  We need to remove
   ** from our tracking table any tasks that were in the kill list, even
   ** if they were not killed because they were no longer idle.  We then
   ** need to truncate the #killThese and #idleProcess tables so they
   ** are clean for the next iteration of the loop.
   */

   delete #sleepTracker where SPID in (select SPID from #killThese)
   truncate table #killThese
   truncate table #idleProcess

   /* now go to sleep */
   execute (@runInterval_waitfor)
	
   /* loop to the top and do it again! */
end
	
/* for completeness sake */
deallocate cursor kill_crsr

go


/********************************************************************
** create appropriate permissions
*********************************************************************/
Set Nocount On
Print " "
Print "No permissions granted in server: %1!", @@servername
Set Nocount Off
Print "Hiding text of this proc"
Exec sp_hidetext sp__idlereaper
go