Symptom
Ø When
using a plain SHUTDOWN, the shutdown takes a long time or appears to hang
Ø When
using SHUTDOWN WITH NOWAIT, recovery takes a long time
Cause
The SHUTDOWN command blocks most new connections and then
waits for each existing active spid to complete processing its current
statement. If the next statement in the current transaction is not a COMMIT,
ASE then makes the transaction roll back. In the worst case, this means
that a long-running statement, like an update of every row in a large table,
will keep running until all rows are updated, and then rollback, undoing the
update of every row. (The assumption is that most
statements will be atomic and commit once finished). ASE then
actually shuts down once all processes have finished processing their current
statement and either committed or rolled back.
Resolution
Ø Before
you issue the shutdown, check the master..syslogshold table for long-running
open transactions. You can issue the KILL command to force these to start
rolling back before issuing the shutdown.
Ø Users
with sa_role are allowed to login and execute commands while a shutdown is in
progress, you can login and issue KILL against any process that is still
executing a command (sp_who will show INSERT, UPDATE, DELETE, etc.), the KILL
will force it to abandon processing the current statement and start rollback.
Ø Once you
have started KILL processing on a spid, the KILL spid WITH STATUSONLY command
will give an estimate of how long that spid will take to rollback.
Ø It is
generally better to use a plain SHUTDOWN with manual KILLs than SHUTDOWN WITH
NOWAIT, as the rollback from each KILL can be executed in parallel on a
multi-engined ASE. The recovery done by SHUTDOWN WITH NOWAIT is
single-threaded. ASE also has to perform free space accounting after
SHUTDOWN WITH NOWAIT as it does not flush the in-memory accounting values.