- 论坛徽章:
- 0
|
求助：关于“鬼进程”
/*
** sp__idleReaper
**
** 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')
**
**
*/
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 */
as
declare @waitevent smallint /* receive sleep wait event id */
declare @kill_spid smallint /* spid to kill */
declare @kill_string varchar(20) /* string to execute kill */
declare @runInterval_HR int /* hours between runs */
declare @runInterval_MN int /* minutes between runs */
declare @runInterval_ST varchar(30) /* waitfor string */
declare @logmsg varchar(150) /* RobV 23-Sep-2003: for logging killed processes */
/* RobV 23-Sep-2003: identify ourselves... */
set clientname sp__idleReaper
/* RobV 23-Sep-2003: write notification to ASE errorlog */
set background on
print "Starting sp__idleReaper ..."
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 will I wait? */
select @runInterval_HR = @maxIdle / 60
/* how many minutes will I wait? */
select @runInterval_MN = @maxIdle % 60
/* build the waitfor string */
select @runInterval_ST = "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 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 difference 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
/* RobV 23-Sep-2003: log killed process */
select @logmsg = "sp__idleReaper: killing spid " + convert(varchar,@kill_spid) +
" (login[" + suser_name(suid) + "], host/IP[" + rtrim(hostname) +
"/" + ipaddr + "], hostprocess[" + convert(varchar,hostprocess) +
"]): idle for more than " + convert(varchar,@maxIdle/60000) + " minutes"
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
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_ST)
/* loop to the top and do it again! */
end
/* for completeness sake */
deallocate cursor kill_crsr
go |
|
免费注册
发表于 2005-08-01 12:26
发表于 2005-08-01 13:55
