[sql] 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 ?

This question is related to sql sql-server sql-server-2008

The answer is


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.


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


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.


First execute the below command:

sp_who2

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

KILL {SPID value}

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


sp_who2 'active'

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

kill {SPID value}

  1. 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)
    
  2. 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.

  3. Kill/stop specific query using Session-Id using this query:

    Kill Session-id
    

    Example:

    kill 125 --125 is my Session-Id
    

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.


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


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.

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.


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to sql-server-2008

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object How to Use Multiple Columns in Partition By And Ensure No Duplicate Row is Returned SQL Server : How to test if a string has only digit characters Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query Get last 30 day records from today date in SQL Server How to subtract 30 days from the current date using SQL Server Calculate time difference in minutes in SQL Server SQL Connection Error: System.Data.SqlClient.SqlException (0x80131904) SQL Server Service not available in service list after installation of SQL Server Management Studio How to delete large data of table in SQL without log?