sequel.learner sequel.learner - 1 month ago 17
SQL Question

How to kill/stop a long SQL query immediately?

I am using SQL server 2008 and its management studio. I executed a query that yields many rows. I tried to cancel it via the red cancel button, but it has not stopped for the past 10 minutes. It usually stops within 3 minutes.

What could the reason be and how do I stop it immediately ?

Answer

What could the reason

A query cancel is immediate, provided that your attention can reach the server and be processed. A query must be in a cancelable state, which is almost always true except if you do certain operations like call a web service from SQLCLR. If your attention cannot reach the server is usually due to scheduler overload.

But if your query is part of a transaction that must rollback then rollback cannot be interrupted. If it takes 10 minutes then it needs 10 minutes and there's nothing you can do about it. Even restarting the server will not help, will only make startup longer as recovery must finish the rollback.

To answer which specific reason applies to your case, you'll need to investigate yourself.