Trying to kill spid in send sleep mode, the kill semm to do nothing, the spid is doing select on a big table. ASE version 15.7 SP52. Is there a way without having to reboot ASE ?
ASE's KILL command doesn't do much itself, it just sets a bit (named "PKILLYOURSELF") in the spid's PSS structure. The spid will check the state of this bit when it wakes up, and if it finds the bit set will then proceed with cleaning itself up. However, if the event the spid is sleeping on never occurs, it doesn't wake up and check the bit and just keeps sleeping. And in that case, there isn't much you can do but reboot.
ASE 16 introduced a stronger "kill with force" option that is more effective on sleeping processes, but you'd have to upgrade.
One observation I can share about "kill <spid> with force". --- we are at ASE 16 SP02 PL05
It results in a stack trace. And in one case we could load a development database when this command was used to terminate a long running query. Server reboot was needed to clear up the scene.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.