如何立即终止/停止长 SQL 查询？

How to kill/stop a long SQL query immediately?

提问by sequel.learner

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 ?

原因可能是什么，我该如何立即停止？

采纳答案by Remus Rusanu

What could the reason

可能是什么原因

A query cancel is immediate, provided that your attentioncan 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 scheduleroverload.

查询取消是即时的，前提是您的注意力可以到达服务器并得到处理。查询必须处于可取消状态，这几乎总是正确的，除非您执行某些操作，例如从 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 whichspecific reason applies to your case, you'll need to investigate yourself.

要回答这些具体的理由适用于你的情况，你需要调查自己。

回答by Mudassir Hasan

sp_who2 'active'

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

检查CPUTime和DiskIO下的值。注意进程的SPID 值比较大。

kill {SPID value}

回答by user1608817

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}

回答by anakic

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.

这是一个愚蠢的答案，但至少在我的情况下它可靠地工作：在管理工作室中，当“取消执行查询”没有停止查询时，我只需单击以关闭当前的 sql 文档。它问我是否要取消查询，我说是，然后在几秒钟内它停止执行。之后它会询问我是否要在关闭前保存文档。此时，我可以单击取消以保持文档处于打开状态并继续工作。不知道幕后发生了什么，但它似乎有效。

回答by Tom Stickel

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 仅供参考）

Spot the SPID you wish to kill e.g. 81

找出您想要杀死的 SPID，例如 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

为了让 STATUS 再次运行，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.

回答by Yash Saraiya

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

您可以使用键盘快捷键ALT+Break来停止查询执行。但是，这可能不会在所有情况下都成功。

回答by Jai Nath Gupta

--Find Session Id for respective all running queries
SELECT text, getdate(),*
 FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)

---Kill specific session
kill 125

回答by Joseph McKinley

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 64bit 上，从 IIS 长时间运行查询，kill spid 似乎不起作用，查询只是一次又一次地重新启动。它似乎正在重用spid。该查询导致 sql server 不断占用 35% 的 cpu 并挂起网站。我猜 bc/ 它无法响应其他登录查询

回答by Aubrey Love

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”按钮，然后单击陷入循环的程序，它就会终止该程序。希望有帮助。

回答by Maulik Modi

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

但自从我试图找到解决方案以来。只有 1 种外行方法有效

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

PS：这对我有用，如果对你也有用，那就点赞。！！！