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
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
"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}
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
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.
Source: Stackoverflow.com