5

Recently we had a SQL job inserting into a database. The query had been running for a few days unecessarily so it was killed using KILL SPID. The process then began to roll back for several days and just seemed to hang there.

Running KILL SPID WITH STATUSONLY gave a message stating that 'Estimated completion' was 100% and 'Estimated time left' was 0 seconds.

Eventually we had to restart the SQL service which removed the process.

My question is- how should you kill a SQL process? Is this the only way?

Improve this question

2 Answers 2

Reset to default
5

You did everything right, but you have consider the cost of the rollback. You said that your query was running for days, so it would have written days of data to the log files as an uncommitted transaction. By killing the task, you initiated a rollback and this is what you have to wait for.

If you want to be able to kill the transaction with less impact, consider batching the transactions and doing occasional commits. If you can avoid a transaction altogether that would be better for long running jobs.

Improve this answer
3

I've seen a rollback "hang" as you describe, and the only way to get rid of it is to restart the SQL service, as you did.

BUT it is important to make sure that the rollback really is hung, and not still actually rolling back (as you saw, the WITH STATUSONLY isn't completely reliable). The risk is that if you restart the SQL service while the rollback is still actually rolling back, then the database will be stuck in "recovery" mode after you restart the SQL service, until the rollback is complete.

The only other way I've been able to tell is by using refreshing the SQL Activity Monitor, and seeing if the CPU and/or IO count for the SPID is still advancing. If it is, then the rollback is still in progress, and you shouldn't restart SQL yet. Give it more time.

One additional point: don't be tempted to do crazy things like deleting the log file. This will "stop" the transaction rollback, but at the cost of the integrity of your database. The only other technique I've used (assuming the rolled-back transaction is the only important change to the database since the last backup) is to simply restore from the latest backup, which we determined would be faster and easier than waiting an additional day for a rollback to occur. This, of course, is highly dependent on the nature of your database activity.

Improve this answer
2
  • Cheers for that, some good pointers :)
    – Anonymous
    Commented Jul 8, 2009 at 8:09
  • One additional thought, based on some things I've seen more recently: check to make sure your hung rollbacks aren't actually calls to some external process (3rd-party backup agent or something). To clear those, you'll have to kill the 3rd party process on the host machine.
    – BradC
    Commented Jan 30, 2017 at 14:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .