Hi all,
Is there a way we can find all the running or suspended sessions that have been running for longer than 30 minutes? Once we find those sessions, how do we kill those sessions using session id?
thanks a lot
N.H
My recommendation is that "KILL" is not the answer. Consider this... if a session has been doing something in an 8 CPU multi-threaded fashion for a half hour, how long do you imagine it will take to do the "Rollback" thanks to a thoughtless "Kill"? The answer is, you can't possibly know . It could be instant or it could take 2 days.
And just killing crap code could lead you into big trouble especially during reporting time or import time. And, it does absolutely NOTHING to prevent similar problems in the future. Get a copy of sp_WhoIsActive (by Adam Machanic) and find out what the query code actually is, which machine they're running from, and find the person running the code. Call them on the phone and find out what's going on. Then, figure out what's wrong with the code and why it's taking so long and either fix it or have the author(s) of the code fix it.
The job you save, may be your own.
1 Like
Instead of killing all the sessions, I have inserted those into a staging table and emailed the users with session ids. So ask them why these are taking too long and asked them to fix the code.
kind regards
N.H
That's a great first step... unfortunately, most will ignore it. You'll end up being the one to design a fix UNLESS you can get management onboard.