How to kill/stop a long SQL query immediately?

Sql Sql Server Sql Server-2008

Sql Problem Overview

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 ?

Sql Solutions

Solution 1 - Sql

sp_who2 'active'

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

kill {SPID value}

Solution 2 - Sql

> 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.

Solution 3 - Sql

First execute the below command:

sp_who2

After that execute the below command with SPID, which you got from above command:

KILL {SPID value}

Solution 4 - Sql

This is kind of a silly answer, but it works reliably at least in my case: In management studio, when the "Cancel Executing Query" doesn't stop the query I just click to close the current sql document. it asks me if I want to cancel the query, I say yes, and lo and behold in a few seconds it stops executing. After that it asks me if I want to save the document before closing. At this point I can click Cancel to keep the document open and continue working. No idea what's going on behind the scenes, but it seems to work.

Solution 5 - Sql

If you cancel and see that run

 sp_who2 'active'

(Activity Monitor won't be available on old sql server 2000 FYI )

Spot the SPID you wish to kill e.g. 81

Kill 81

Run the sp_who2 'active' again and you will probably notice it is sleeping ... rolling back

To get the STATUS run again the KILL

Kill 81

Then you will get a message like this

 SPID 81: transaction rollback in progress. Estimated rollback completion: 63%. Estimated time remaining: 992 seconds.

Solution 6 - Sql

First, you need to display/check all running queries using below query-

SELECT text, GETDATE(), *
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)

Find Session-Id and Description for respective all running queries and then copy specific query's Session-Id which you want to kill/stop immediately.
Kill/stop specific query using Session-Id using this query:
```
Kill Session-id
```
Example:
```
kill 125 --125 is my Session-Id
```

Solution 7 - Sql

You can use a keyboard shortcut ALT + Break to stop the query execution. However, this may not succeed in all cases.

Solution 8 - Sql

I Have Been suffering from same thing since long time. It specially happens when you're connected to remote server(Which might be slow), or you have poor network connection. I doubt if Microsoft knows what the right answer is.

But since I've tried to find the solution. Only 1 layman approach worked

Click the close button over the tab of query which you are being suffered of. After a while (If Microsoft is not harsh on you !!!) you might get a window asking this

"The query is currently executing. Do you want to cancel the query?"

Click on "Yes"
After a while it will ask to whether you want to save this query or not?
Click on "Cancel"

And post that, may be you're studio is stable again to execute your query.

What it does in background is disconnecting your query window with the connection. So for running the query again, it will take time for connecting the remote server again. But trust me this trade-off is far better than the suffering of seeing that timer which runs for eternity.

PS: This works for me, Kudos if works for you too. !!!

Solution 9 - Sql

apparently on sql server 2008 r2 64bit, with long running query from IIS the kill spid doesn't seem to work, the query just gets restarted again and again. and it seems to be reusing the spid's. the query is causing sql server to take like 35% cpu constantly and hang the website. I'm guessing bc/ it can't respond to other queries for logging in

Solution 10 - Sql

A simple answer, if the red "stop" box is not working, is to try pressing the "Ctrl + Break" buttons on the keyboard.

If you are running SQL Server on Linux, there is an app you can add to your systray called "killall" Just click on the "killall" button and then click on the program that is caught in a loop and it will terminate the program. Hope that helps.

Solution 11 - Sql

In my part my sql hanged up when I tried to close it while endlessly running. So what I did is I open my task manager and end task my sql query. This stop my sql and restarted it.

Solution 12 - Sql

If using VSCode mssql Extension, click F1, write mssql in the prompt and choose 'cancel query', as shown in this thread about the extension.

Content Type	Original Author	Original Content on Stackoverflow
Question	sequel.learner	View Question on Stackoverflow
Solution 1 - Sql	Mudassir Hasan	View Answer on Stackoverflow
Solution 2 - Sql	Remus Rusanu	View Answer on Stackoverflow
Solution 3 - Sql	user1608817	View Answer on Stackoverflow
Solution 4 - Sql	anakic	View Answer on Stackoverflow
Solution 5 - Sql	Tom Stickel	View Answer on Stackoverflow
Solution 6 - Sql	Jai Nath Gupta	View Answer on Stackoverflow
Solution 7 - Sql	Yash Saraiya	View Answer on Stackoverflow
Solution 8 - Sql	Maulik Modi	View Answer on Stackoverflow
Solution 9 - Sql	Joseph McKinley	View Answer on Stackoverflow
Solution 10 - Sql	Aubrey Love	View Answer on Stackoverflow
Solution 11 - Sql	bot	View Answer on Stackoverflow
Solution 12 - Sql	Franz Maikäfer	View Answer on Stackoverflow