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 ?

have you checked that any application is holding this query?? — user1102001, Apr 10 '13 at 09:05
i have faced this senario as even i was running everything in sql and it's my wish to edit my proflie or not... — user1102001, Apr 10 '13 at 09:09
@user1102001 - not forcing you to do anything. I said can and not should :) — sequel.learner, Apr 10 '13 at 09:10

Mudassir Hasan · Answer 1 · 2016-10-20T13:33:25.467

147

sp_who2 'active'

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

kill {SPID value}

edited Oct 20 '16 at 13:33

answered Apr 10 '13 at 09:12

Mudassir Hasan

28,083
20
99
133

1

I am using Microsoft SQL Server 2008 R2, I tried to find out what isthe SPID of the query that is being executed and is suspended, and found that there are many instances of the same SELECT SQL having the same SPID. When I try to execute the command KILL 114 - here 114 is the SPID value of my suspended query. I get the error shon below: Please guide. Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '114'. – Code Buster Jun 06 '16 at 10:40

score 76 · Accepted Answer · edited Nov 04 '20 at 22:22

76

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.

edited Nov 04 '20 at 22:22

Sergey

1,608
1
27
40

answered Apr 10 '13 at 09:19

Remus Rusanu

288,378
40
442
569

1

Note that SSMS shows the query as 'canceled' (As opposed to 'attempting to cancel') only after the attention reply is received, which implies draining all the intermediate results in the communication pipe first (eg. all results in the TCP/IP send/received buffers and all in-memory buffers). – Remus Rusanu Apr 10 '13 at 09:34
3

I wish it would say 'rolling back' – Simon_Weaver Aug 01 '16 at 01:36
1

@RemusRusanu - What if the estimated rollback time is 20,000+ hours? Happy to restore from a backup. https://dba.stackexchange.com/questions/222145/how-to-immediately-terminate-a-spid-with-a-very-long-rollback-time-estimated – youcantryreachingme Nov 08 '18 at 23:48

score 46 · Answer 3 · edited Jun 24 '16 at 18:02

46

First execute the below command:

sp_who2

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

KILL {SPID value}

edited Jun 24 '16 at 18:02

Kenny Grage

1,124
9
16

answered Apr 10 '13 at 09:08

user1608817

471
3
4

score 28 · Answer 4 · answered Jul 25 '14 at 08:49

28

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.

answered Jul 25 '14 at 08:49

anakic

2,746
1
30
32

8

In this case the query might actually still be running/hanging in the background, you just won't see it anymore. – Aries51 Mar 18 '15 at 14:38
1

I think it kills the connection, as you do have to reconnect after this. – anakic Mar 19 '15 at 08:03
2

It kills the connection to SSMS but not (necessarily) to the db. – Aries51 Apr 21 '15 at 11:38
2

I did try this my sql hanged up. T_T – bot Oct 22 '15 at 09:59
2

If you can't see it, it doesn't exist. – ChrisD Aug 19 '19 at 09:45
This happened for me when I cancelled a query that contained a while loop and and commit transaction. Until I closed the ssms document / window, it didn't execute the 'COMMIT TRANSACTION' of the current loop, and just sat there frozen. – Jonathan May 04 '23 at 21:45

Tom Stickel · Answer 5 · 2019-07-01T19:30:22.177

24

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.

edited Jul 01 '19 at 19:30

answered Jun 09 '17 at 00:15

Tom Stickel

19,633
6
111
113

1

I had to run `KILL {spid} WITH STATUSONLY` to see the status message. – Rudey Oct 05 '20 at 15:18

score 20 · Answer 6 · edited Dec 24 '20 at 21:14

20

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

edited Dec 24 '20 at 21:14

marc_s

732,580
175
1,330
1,459

answered Jan 28 '20 at 07:11

Jai Nath Gupta

221
2
7

2

Please add some explanation to your answer by editing it such that others can learn from it – Nico Haase Jan 28 '20 at 08:26

score 15 · Answer 7 · edited Mar 18 '16 at 07:05

15

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

edited Mar 18 '16 at 07:05

Bhargav Rao

50,140
28
121
140

answered Mar 18 '16 at 06:24

Yash Saraiya

1,035
1
20
40

2

This one is worked for me in Management studio. Thanks – Vishvanathsinh Solanki Oct 16 '18 at 06:34

score 7 · Answer 8 · answered Dec 18 '17 at 14:25

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

Still worked for me and solved my problem as of --> – ErocM Jan 01 '20 at 05:31 — ErocM, Jan 01 '20 at 05:31

score 2 · Answer 9 · answered Oct 02 '13 at 19:14

2

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

answered Oct 02 '13 at 19:14

Joseph McKinley

21
1

2

I'd love to see a source for this. – Ant Swift Jan 15 '14 at 13:34

score 2 · Answer 10 · answered Oct 27 '17 at 20:22

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.

score 1 · Answer 11 · answered Oct 22 '15 at 10:04

1

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.

answered Oct 22 '15 at 10:04

bot

4,841
3
42
67

score 1 · Answer 12 · answered Jun 10 '21 at 15:40

1

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

answered Jun 10 '21 at 15:40

Franz Maikäfer

41
1
5

1

This doesn't really answer the question at hand. If they were asking how to do so via VSCode then it would be better, but its more about Sql Server in general. – Timothy G. Jun 11 '21 at 03:27
1

It doesn't fit all nitpicky details after the main question - How to kill/stop a long SQL query immediately? - that i found via google trying to solve my little variant. And yes, i searched for one question more relatable to my own details. So, when i found the solution to this problem (and this nuance in my client) i added with a big disclaimer - IF using such client - you can cancel in this way. Trying to help others... never understood such zealotism beyond obviously orthogonal answers. But if you think that it'd be better to delete it, flag to moderation, Timothy. Peace. – Franz Maikäfer Jun 15 '21 at 00:08
Timothy G is destroyed – milos Jun 20 '22 at 09:39

score 0 · Answer 13 · answered Nov 23 '22 at 07:19

0

My studio version: Microsoft SQL Server Management Studio 18

I manually closed the computer network card, and the infinite loop query was successfully terminated!

answered Nov 23 '22 at 07:19

Yimeng

1

How to kill/stop a long SQL query immediately?

13 Answers13

Linked

Related