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.
我正在使用SQL server 2008及其管理工作室。我执行了一个查询，该查询产生许多行。我试着通过红色的取消按钮取消它，但是它在过去的10分钟里没有停止。它通常在3分钟内停止。
What could the reason be and how do I stop it immediately ?
原因是什么?我该如何立即停止?
10 个解决方案
#1
48
What could the reason
还有什么原因
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 call a web service from SQLCLR. If your attention cannot reach the server is usually due to scheduler overload.
查询取消是即时的，前提是您的注意力可以到达服务器并进行处理。查询必须处于可取消状态，这几乎总是正确的，除非您执行某些操作，如从SQLCLR调用web服务。如果您的注意力无法到达服务器，通常是由于调度程序过载。
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, will only make startup longer as recovery must finish the rollback.
但是，如果查询是必须回滚的事务的一部分，则回滚不能被中断。如果需要10分钟，那就需要10分钟，你也无能为力。即使重新启动服务器也无济于事，只会使启动时间延长，因为恢复必须完成回滚。
To answer which specific reason applies to your case, you'll need to investigate yourself.
为了回答这个问题，你需要调查一下自己。
#2
80
sp_who2 'active'
Check values under CPUTime and DiskIO. Note the SPID of process having large value comparatively.
在CPUTime和DiskIO下检查值。注意，过程的SPID相对较大。
kill {SPID value}
#3
23
First execute the below command:
首先执行以下命令:
sp_who2
After that execute the below command with SPID, which you got from above command:
然后使用SPID执行下面的命令，这是从上面的命令中得到的:
KILL {SPID value}
#4
15
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.
这是一个有点傻的答案，但至少在我的例子中是可靠的:在management studio中，当“取消执行查询”没有停止查询时，我只需单击以关闭当前的sql文档。它问我是否想要取消查询，我说是的，然后在几秒钟内它停止执行。然后它会问我是否要在结束之前保存文档。此时，我可以单击Cancel保持文档打开并继续工作。不知道幕后发生了什么，但这似乎行得通。
#5
8
You can use a keyboard shortcut ALT + Break to stop the query execution. However, this may not succeed in all cases.
您可以使用键盘快捷键ALT + Break来停止查询执行。然而，这可能并非在所有情况下都成功。
#6
6
If you cancel and see that run
如果你取消并看到运行
sp_who2 'active'
(Activity Monitor won't be available on old sql server 2000 FYI )
(活动监视器在旧的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
再次运行sp_who2“active”，您可能会注意到它正在睡觉……回滚
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.
#7
1
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
显然，在sql server 2008 r2 64位上，由于来自IIS的长时间运行的查询，kill spid似乎不能工作，所以查询会一次又一次地重新启动。它似乎在重复使用spid。该查询导致sql server经常占用35%的cpu，并挂起网站。我猜bc/它不能响应其他登录查询
#8
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.
在我的部分，我的sql挂起当我试图关闭它的同时无休止地运行。我打开任务管理器，结束sql查询。这将停止sql并重新启动它。
#9
0
A simple answer, if the red "stop" box is not working, is to try pressing the "Ctrl + Break" buttons on the keyboard.
一个简单的答案是，如果红色的“停止”框不起作用，可以尝试按下键盘上的“Ctrl + Break”按钮。
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.
如果你在Linux上运行SQL Server，你可以添加一个名为“killall”的应用程序，点击“killall”按钮，然后点击循环捕获的程序，程序就会被终止。希望有帮助。
#10
0
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.
发布它，也许你的studio再次稳定地执行你的查询。
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. !!!
这对我很有效，如果对你也很有效的话。! ! !
#1
48
What could the reason
还有什么原因
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 call a web service from SQLCLR. If your attention cannot reach the server is usually due to scheduler overload.
查询取消是即时的，前提是您的注意力可以到达服务器并进行处理。查询必须处于可取消状态，这几乎总是正确的，除非您执行某些操作，如从SQLCLR调用web服务。如果您的注意力无法到达服务器，通常是由于调度程序过载。
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, will only make startup longer as recovery must finish the rollback.
但是，如果查询是必须回滚的事务的一部分，则回滚不能被中断。如果需要10分钟，那就需要10分钟，你也无能为力。即使重新启动服务器也无济于事，只会使启动时间延长，因为恢复必须完成回滚。
To answer which specific reason applies to your case, you'll need to investigate yourself.
为了回答这个问题，你需要调查一下自己。
#2
80
sp_who2 'active'
Check values under CPUTime and DiskIO. Note the SPID of process having large value comparatively.
在CPUTime和DiskIO下检查值。注意，过程的SPID相对较大。
kill {SPID value}
#3
23
First execute the below command:
首先执行以下命令:
sp_who2
After that execute the below command with SPID, which you got from above command:
然后使用SPID执行下面的命令，这是从上面的命令中得到的:
KILL {SPID value}
#4
15
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.
这是一个有点傻的答案，但至少在我的例子中是可靠的:在management studio中，当“取消执行查询”没有停止查询时，我只需单击以关闭当前的sql文档。它问我是否想要取消查询，我说是的，然后在几秒钟内它停止执行。然后它会问我是否要在结束之前保存文档。此时，我可以单击Cancel保持文档打开并继续工作。不知道幕后发生了什么，但这似乎行得通。
#5
8
You can use a keyboard shortcut ALT + Break to stop the query execution. However, this may not succeed in all cases.
您可以使用键盘快捷键ALT + Break来停止查询执行。然而，这可能并非在所有情况下都成功。
#6
6
If you cancel and see that run
如果你取消并看到运行
sp_who2 'active'
(Activity Monitor won't be available on old sql server 2000 FYI )
(活动监视器在旧的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
再次运行sp_who2“active”，您可能会注意到它正在睡觉……回滚
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.
#7
1
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
显然，在sql server 2008 r2 64位上，由于来自IIS的长时间运行的查询，kill spid似乎不能工作，所以查询会一次又一次地重新启动。它似乎在重复使用spid。该查询导致sql server经常占用35%的cpu，并挂起网站。我猜bc/它不能响应其他登录查询
#8
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.
在我的部分，我的sql挂起当我试图关闭它的同时无休止地运行。我打开任务管理器，结束sql查询。这将停止sql并重新启动它。
#9
0
A simple answer, if the red "stop" box is not working, is to try pressing the "Ctrl + Break" buttons on the keyboard.
一个简单的答案是，如果红色的“停止”框不起作用，可以尝试按下键盘上的“Ctrl + Break”按钮。
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.
如果你在Linux上运行SQL Server，你可以添加一个名为“killall”的应用程序，点击“killall”按钮，然后点击循环捕获的程序，程序就会被终止。希望有帮助。
#10
0
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.
发布它，也许你的studio再次稳定地执行你的查询。
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. !!!
这对我很有效，如果对你也很有效的话。! ! !