KILLed Sybase process will not die?
Steve Wall
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 (wa...@mcm.com)
Mellon Capital Management Corporation
San Francisco, CA
Ying Yang
>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?]
>
When we kill a Sybase process, we may get (at least) four different
results :
1. The process is a ordinary retrieve transaction, i.e. SELECT, it
dies immediately.
2. The process is an update transaction, it does not die until the
server has rolled back the transaction. The time is directly related to
the size of transaction.
3. The process is a DBCC transaction. Sybase forks a separate process
for the transaction, and the new one is out of users' control. I have
experience to check multi-million row tables(with checktable option).
DBCC checks the tables index by index, we can only kill it when it
finishes one index and ready for the next one. It may take amywhere
from several minutes to four hours to die. In your case, it seems you
were running checkallec or checkdb, so the process held locks on the
sysobjects, and you could not control the "ghost" Sybase process.
4. The process is sleeping. We cannot kill a sleeping process. When an
end-user process gets disconnected, we cannot kill the Sybase process
and release the locks. To deal with this, we have installed a EBF to
kill disconnected processes when our clients turn off their PCs. At
last year's User Conference, Sybase said the 5.0 version would provide
unconditional KILL, hope this is true.
Ying Yang
MIS
Agency Rent-A-Car
Prab Goripathi
Warren Finnerty
KILL appears only to take effect when the process goes into a "runnable"
state ( At which time it prob. checks some status area ). This explains
the behavior that you are seeing in that the DBCC will only go out of
"sleeping" state after a long time. Processes that have altered data
will need to undo what they have done first or very bad things would happen.
--
warren finnerty | 388 Greenwich St.
Lehman Brothers | NYC NY 10013
"Back off man!" | wfin...@shearson.com
Ying Yang
>
>4. The process is sleeping. We cannot kill a sleeping process. When an
>end-user process gets disconnected, we cannot kill the Sybase process
>and release the locks. To deal with this, we have installed a EBF to
>kill disconnected processes when our clients turn off their PCs. At
>last year's User Conference, Sybase said the 5.0 version would provide
>unconditional KILL, hope this is true.
I have received several inquiries about the EBF. It seems a lot of having
the disconnect problem, so I am sending follow up to this group.
We have an 4.8 server on VAX/VMS platform. We have installed EBF989,
which can kill a sleeping process and clean up the locks. In order
for this EBF to work, we have to set the "keep-alive" flag
in our Wallongon TCP/IP. We an end-user turns off his (her) PC,
TCP/IP's keep alive inquiries get no responds form that PC. After
two minutes, TCP/IP sends a signal to the server notifying the
disconnect. Upon receiving the signal, the EBF kills the process
and cleans up the locks.
A short history. Last April, our production was in in a very bad
shape due to locks left by the disonnects. We managed to get the
attention of the Sybase Management (we are so called "critical account").
A Sybase level III engineer worked with us for several weeks, and
produced this EBF, which has been very critical to us.
Joseph Alotta
> 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
i just press that little button with the vertical line and the
circle and it works every time.
joe.
--
==============================================================
! joe alotta j...@fnbc.com !
! (312) 732-3439 !
! !
! "The main thing is to keep the main thing the main thing." !
! Dr. George Sweeney !
==============================================================