September 28, 2004 at 2:34 pm
Hi
I have a small question. it can be a kind of dumb but..
i am trying to find if a processes is sleeping or running for how much time from the sysprocesses table and trying to kill the ones which are sleeping for 60mts and running more than 24hrs.
when i check the cpu time column in the sysprocesses table...it is not changing for the processes which are sleeping and sometimes the cputime becomes '0' when the thread changes the status from runnable to sleeping.
i am confused as to how to get the correct time, so that i can kill the thread.
can somebody send me some information abt this or send me their solution to it.
thanks in advance.
hari
September 28, 2004 at 3:31 pm
The last_batch column is the date/time of the prior statement.
Probably this in conjunction with cmd = "AWAITING COMMAND" is sufficient (you don't want to kill something currently involved in a really long process or in a live lock waiting to complete its process).
September 28, 2004 at 3:42 pm
can you please tell me what information does cpu column give in the sysprocesses table.
I cannot use the last_batch column in order to check as to how long a process was sleeping?
September 30, 2004 at 2:45 am
sp_who2 active will list only active spids.
I used a proc that killed spids based upon the last batch run time ( just to explain there was an app which opened a connection for every screen but never closed them off until the user logged off - this was a solution while we educated the developer !! ) I sometimes use this technique to clean up connections fro QA left open on machines left logged on at night.
Check out the sql2k resource kit which has some info on all this.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply