How can I safely kill a long running MSSQL query?
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?
Solution 1:
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.
Solution 2:
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.