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?
Best Answer
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.