From: wall@mcm.com (Steve Wall)
Subject: KILLed Sybase process will not die?
Organization: Mellon Capital Management Corp. San Francisco, CA.
Date: Tue, 5 Jan 1993 19:25:46 GMT

Several times I've wanted to KILL a Sybase process with the
equivalent of the UNIX kill -9 (die NOW!, no sig catch, etc.).
Sybase gives you KILL, but under certain circumstance, the kill
doesn't seem to take effect until some kind of variable delay
(seems to be dependent on the type of process). Probably due
to the Sybase kernel scheduler, resources in use by the proc,
process run state (blocked or not), etc.

For instance, this morning I have a DBCC process that is running
later than usual due to delays up the pipeline (yes, I'm aware
that DBCC in multiuser is not technically correct, and we interpret
the results with this in mind), And now the DBCC is hogging the
Server when people are trying to perform typical, daily queries.
Thus, I want to KILL it since it is not mandatory that we get
DBCC results every day.

[Actually, the DBCC (spid=1) is blocking all my users due to locking
on sysobjects (DBCC has a shared page lock on sysobjects
page 2, my other proc (spid=10) is trying to get an Ex-table on
sysobjects - see below). Why is DBCC locking sysobjects for so long?]


sp_lock
go
spid   locktype             table_id    page        dbname          
------ -------------------- ----------- ----------- --------------- 
     1 Sh_page-blk                    1           2 idc             
     1 Sh_table              2000010156           0 idc             
    10 Ex_table-blk                   1           0 idc             
    10 Ex_page                        3          53 idc             
    26 Sh_intent              256003943           0 master   



sp_who
spid   status       loginame     hostname   blk dbname     cmd              
------ ------------ ------------ ---------- --- ---------- ---------------- 
     1 sleeping     sa           persian    0   idc        DBCC             
     2 sleeping     sa                      0   master     NETWORK HANDLER  
     3 sleeping     sa                      0   master     MIRROR HANDLER   
     4 sleeping     sa                      0   master     CHECKPOINT SLEEP 
     5 sleeping     matt         guava      0   ADMIN      AWAITING COMMAND 
     6 sleeping     beth                    0   ADMIN      AWAITING COMMAND 
     7 sleeping     wall         persian    10  idc        AWAITING COMMAND 
     8 sleeping     wall         orange     10  idc        AWAITING COMMAND 
     9 sleeping     niklas       orange     10  idc        AWAITING COMMAND 
    10 sleeping     ambrosia     persian    1   idc        AWAITING COMMAND 
    13 sleeping     wall         orange     10  idc        AWAITING COMMAND 
    14 sleeping     wall         orange     10  idc        AWAITING COMMAND 
    15 sleeping     david                   0   MARKETING  AWAITING COMMAND 
    16 sleeping     wall         orange     10  idc        AWAITING COMMAND 
    17 sleeping     wall         persian    10  idc        AWAITING COMMAND 
    18 sleeping     oneto        orange     10  idc        AWAITING COMMAND 
    19 sleeping     wall         persian    10  idc        AWAITING COMMAND 
    20 sleeping     wall         persian    10  idc        AWAITING COMMAND 
    21 sleeping     pamb         orange     10  idc        AWAITING COMMAND 
    22 running      sa           persian    0   master     SELECT   



Fun, huh? Kills to the DBCC do nothing. The kill requests are logged
in the errorlog, so the server is getting them, but there is no effect.
I'm also getting spurious invalid Sybase processe messages, so the
server may be hung due to another error/bug.

Whatever the case, my only resolution is to restart the server
(shutdown with nowait) because I cannot kill the DBCC that everyone
is blocked on.

Any comments about these issues, especially the issue of when a
KILL really takes effect, are appreciated.

Steve
-- 

Steve Wall (wall@mcm.com)
Mellon Capital Management Corporation
San Francisco, CA