How do I kill an unruly spid in Sybase?

Question

I've got a couple of rogue spid's in my database that I can see are sleeping when I log in as sa and use sp_who, but attempts to use kill <spid> to eliminate them have failed and I don't actually have access to the server itself to bounce it. Is there any other way of killing these things off?

Answer 1

I have a client application that sometimes does similar things. The client is usually in the middle of a select, but is no longer retrieving rows. (This may be a client side cursor, but I'm not sure). These spids seem un-killable. I have to find the user and ask them to log off.

The other scenario in which I've had these is after errors or stacktraces on the server. Check your errorlog to see if there's any dodgy entries in there. If there is then a restart is going to be your only option.

Answer 2

System processes cannot be killed. For example in the output of sp_who where cmd is NETWORK HANDLER, MIRROR HANDLER and CHECKPOINT SLEEP (or rarely, CHECKPOINT) and AUDIT PROCESS cannot be killed. If they have status as "recv sleep", "send sleep", "alarm sleep" and "lock sleep" , they can be killed.

In sp_who output, you cannot tell whether a process whose status is "recv sleep" belongs to a user who is using SQL Server, but may be pausing to examine the results of a command, or whether the process indicates that a user has rebooted a PC or other terminal, and left a stranded process.You can learn more about a questionable process by querying the sysprocesses table for information. For ex :

select hostprocess, program_name from sysprocesses where spid = 8

If program_name is isql you know that this spid can be killed.(and so forth ... )