How to kill/stop a long SQL query immediately?

Question

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 1

sp_who2 'active' 

Check values under CPUTime and DiskIO. Note the SPID of process having large value comparatively.

kill {SPID value} 

Answer 2

What could the reason be

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 calling a web service from SQLCLR. If your attention cannot reach the server it's 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, it will only make startup longer since recovery must finish the rollback.

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