May 12, 2009 at 1:18 pm
Hi experts,
i have one sp in my environment already..that runs every 15 minutes on dev server and
kills processesthat are idle for more than 15 minutes.
--basically it checks sysprocesses table and see if status = 'sleeping' or cmd ='awaiting cmd'
and if workstation id starts from certain letters and if process is there for more than 15 minutes
then it kills that process(in my environment
for all developers' workstation name begins with "NYNJ%" so it checks for thisso we dont kill any server process
because servers' names starts with different letters)
Because one of my process runs for more than 15 minutes twice a month and it gets killed automatically
because of this job.Because of the status of that process is IDLE,my job kills it.
My question is,
1)these filters are enough or should i check for waittype,waittime,waitresource kind of columns
and then filter according to that so that some innocent processes cant be killed.!
What values for these 3 columns should i check?
Is there any other column values that i have to check and then kill processes..?
Please let me know Thanks..
May 12, 2009 at 3:33 pm
1. May I ask why are you killing these processes? I believe it would be better for you to ascertain why they are being excessively delayed, and then take steps to cure that problem. You could be killing processes that are attempting for example to update rows in a table ... just killing them off hand is going to leave your database a mess.
2. If you are going to continue killing processes as you state you are doing may I suggest that you refer to Books On Line (BOL) subject:
Compatibility Views (Transact-SQL) at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/8e4624f5-9d36-4ce7-9c9e-1fe010fa2122.htm as the sysprocess table has been replaced in SQL 2005 with dynamic management view title sys.dm_exec_requests.
May 12, 2009 at 3:41 pm
Well I should not kill processes and it can create a mess.I agree.
But if i am doing it then I am also taking care of innocent processes
and i need some more suggestions to save innocent processes.thats what the
question is.In other words.. if the process is sleeping means nothing is going on
right? and i can kill it.
likewise what are the other criterias and column values that i should check before killing them!!
May 12, 2009 at 3:45 pm
I'm still not sure you need to kill these processes. Maybe, but you might need lots of them before it would affect your server at all. We used to do something like this, but we wouldn't kill a process unless it had been idle for more than 24 hours.
15 minutes isn't a long time. If I opened SSMS and had a query window open, it could be sleeping for more than 15 minutes, and it would be incredibly annoying to have the connection killed.
You might just want to raise your filter to more like an hour or 2. If you have specific connections that are causing issues, I might track down what they are doing that you think is an issue.
May 12, 2009 at 3:56 pm
Thanks for ur feedback
But its a kind of small environment and not large traffic so may be
they are doing it since long back to avoid more memory,I/o and resources usage
and most important its DEVELOPEMENT area so no one realy cares
Now here is the case.One user has a process that runs more than 15 minute.
It pulls data from one database table.This table has names of SPs which
exists on other database of same server.So it pulls names of SPs from front
end and then executes them.
I was observing the whole process for these 2 databases in sysprocesses
So I found the status of the process = 'sleeping'
and thats why my SP kills that bcoz its showing sleeping for more than 15 minutes.
Wht happens is,its taking data from one database and executes those SP in other database.means
its doing something and should not be sleeping..right?If not then why?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply