Very Computer

HELP!!

I have a process that will not be killed in SQL65. It is blocking another
process and I do not want to re-start teh service.

Any suggestions??

Thank You

J

I'm afraid that you are caught between the devil and the deep blue sea.
The process may have very good excuses for refusing to be killed. It
may for instance may be engaged in a longer rollback exercise, and
believe me you want that rollback to complete, because you would have
a mess of a database.

If you restart the server in this situation, the query still need to
be rolled back when SQL Server starts, but this is done in recovery
mode, when no one is allowed to the database and is considerably
faster. Still, if there is a lot to recover, the recovery may take
some time as well.

--
Erland Sommarskog, Abaris AB

SQL Server MVP

Jason,

In this case, the process is probably rolling back, and hopefully has
finished by the time I write this.  If is is rolling back (and you may be
able to tell by looking at the SPID in sp_who2 output as well as observing
system I/O and processor usage), the best course is usually to wait.

Note that stopping the service during a rollback is a common mistake - it
cannot save you from a lenghty rollback, but instead puts you in the worse
position of waiting until it rolls forward and then rolls back all over
again.

The only ways to get out of waiting for the rollback altogether involve
either restoring from backup or risking serious corruption and data loss,
and all are tedious procedures that tend to take a lot longer than just
waiting for the rollback to complete.

So sit tight and good luck!

Suzanne
Microsoft SQL Server Support