Hello group,

how can I kill a process if


isql -Usa -Pxxxxx

1> kill <spid>
2> go

does not work ?

I retrieve the <spid> from

1> select * from sysprocesses where program_name = "<Program>"
2> go

The <Program> originally called a stored procedure that had a bad SQL
Statement which caused an insert into a temporary table in tempdb with
millions of lines. The calling (unix) program was killed using kill -9
because it was frozen.

Adding the correct procedure into the database does not work because
SQL-Server says "Cannot drop proc XXX because it is currently in use".

How can such a <spid> be killed ?

Any help greatly appreciated.

Best regards and thanks in advance,

Dietmar

Maybe I should add this:

The Syabse version is Sybase ASE 12.0 on Solaris 2.8.

When you've done a 'kill' on an ASE session, you *have*killed it, and it
will eventually disappear. However, when that sessionw as doing a large
transaction (as you described), it will first need to roll back the entire
transaction. For big transactions, this may take a long time since a long of
disk I/O may be required for this. There's hardly a different choice than to
let it continue (well, there is, but yo'd have to throw away your entire
database). In particular, do NOT shut down ASE before the rollback is
complete (and the killed session has disappeared) -- this will only waste
time, since ASE will re-start the rollback when it's restarted.
The thing to keep in mind is that you should really avoid running very long
transactions in a prodution system , unless you know what you're doing and
understand the possible implication.
If you're interested in more technical details, check out my book "Tips,
Tricks & Recipes for Sybase ASE" (www.sypron.nl/ttr), which covers these
things in detail.

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5

Author of various Sybase books. New book (coming soon): "The
Complete Sybase Replication Server Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/shop

mailto:rob (AT) YOUR (DOT) SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

"D. Peglow" <info_peg04 (AT) snafu (DOT) de> wrote

Hi Rob,

many thanks for that explanation. I'll check you book

Kind regards,

Dietmar

Rob Verschoor schrieb:

"D. Peglow" <info_peg04 (AT) snafu (DOT) de> wrote


'kill' works almost always. In most cases where the killed session doesn't
go away, kill has worked fine, but the session is busy rolling back a
transaction. If the transaction is large, this may take a long time. In any
case, there isn't a stronger way of killing the session. When a rollback is
being performed, doing a 'shutdown with nowait' may only make matters worse,
since the rollback will be restarted when ASE starts up. Unless you're
prepared to throw away your entire database (and I mean *really* throw it
away, beyond repair or recovery), a shutdown may be a way out. In all other
cases, better wait patiently until the rollback is finished.
My book "Tips, Tricks & Recipes for Sybase ASE" (www.sypron.nl/ttr) explains
in detail what happens behing the scenes when you kill a process and how to
determine whether a rollback is indeed being performed.

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5

Author of various Sybase books. New book (coming soon): "The
Complete Sybase Replication Server Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/shop

mailto:rob (AT) YOUR (DOT) SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------