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

User · Answer

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

User · Answer

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

User · Answer

First execute the below command   sp who2   After that execute the below command with SPID  which you got from above command   KILL  SPID value

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

sp who2  active    Check values under CPUTime and DiskIO  Note the SPID of process having large value comparatively   kill  SPID value

[sql] How to kill/stop a long SQL query immediately?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008