Killing idle process

Question

Killing idle process

lila

SSChasing Mays

Points: 609
More actions
October 1, 2003 at 5:55 am

#159900

I want to schedule a simple procedure to kill all idle processes - running at 23:00 and killing all processes with last_batch older than 6 hours. I did find a few scripts to do this, but none of them exclude any processes. Can I just kill the system processes too or should I exclude them? (Or will SQLserver not allow me to kill any process that I shouldn't!)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ray_higdon SSCarpal Tunnel Points: 4589 More actions · Answer 1

From BOL:

SQL Server 2000 reserves SPID values from 1 through 50 for internal use, while SPID values 51 or higher represent user sessions.

You could use this in your statement to ignore spids <= 50

HTH

Ray Higdon MCSE, MCDBA, CCNA

------------

Ray Higdon MCSE, MCDBA, CCNA

lila SSChasing Mays Points: 609 More actions · Answer 2

Thanks, but I want this to work on versions 7 and 2000. And I am also curious about whether the system processes can be killed.

ray_higdon SSCarpal Tunnel Points: 4589 More actions · Answer 3

I don't have a 7.0 BOL around, but I'm sure it is in there, lookup sp_who or spid in 7.0 BOL and see if it tells you.

You can kill system processes but I don't think it is reccomended.

Ray Higdon MCSE, MCDBA, CCNA

------------

Ray Higdon MCSE, MCDBA, CCNA

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 4

I wouldn't suggest you do that because that process may be shared by many connections from application unless you are sure the idle process is an orphan process.

bmodi SSC Journeyman Points: 90 More actions · Answer 5

You can use "Program_name" in your where condition.