如何快速关闭Microsoft SQL Server Service Broker的所有元素？
我们有一个应用程序，它使用 Service Broker 在 SQL Server 中创建队列和服务来处理数据库通信。该应用程序使用这些服务并正确发送/接收消息，但我现在想测试该应用程序的初始化阶段（它创建代理以及在幕后工作的存储过程）。基本上，我需要以一定的频率删除代理元素，而且现在它真的很慢。
如果有帮助的话，我可以改变应用程序创建代理元素的方式 - 但这个问题与关闭所有内容更相关。
我用来关闭代理的代码是：
receive * from [dbo].[notify_initiator_queue]
alter queue [dbo].[notify_initiator_queue] with status = OFF
drop service [//DBNotifyService-Initiator]
drop queue [dbo].[notify_initiator_queue]
drop message types, contacts, etc...
这在“drop service [//XF/DBNotifyService-Initiator]”上挂起一段时间。是否有更快的方法来关闭并删除服务代理的全部或部分元素？
谢谢！
==更新==
好吧，我花了一些时间，但下面的答案确实解决了问题。我想向其他可能遇到此问题的人澄清一下。
我的应用程序正确关闭了所有服务、队列、合约和消息。由于应用程序中的错误导致大量开放对话，因此关闭服务花了很长时间。这些对话正在创建，用于发送消息，然后通过以下方式关闭：
END CONVERSATION @conversation with cleanup
“with cleanup”位仅关闭对话的本地端（想一想，它允许服务器清理可能在另一端出错的任何对话）结尾）。它不会关闭发送服务的另一端，因此对话保持开放状态。正常对话应该结束：
END CONVERSATION @conversation
这修复了应用程序错误。然而，我在数据库中有数百万个中断的对话。我可以像普通人一样删除数据库，或者我可以尝试找出如何关闭它们。要一一关闭它们，需要：
declare @conversation uniqueidentifier
while exists (select top 1 conversation_handle from sys.transmission_queue )
begin
set @conversation = (select top 1 conversation_handle from sys.transmission_queue )
end conversation @conversation with cleanup
end
每个连接需要几毫秒（对于数百万连接来说非常慢）。如果我想快速关闭它们，请使用下面的答案并运行修改后的命令：
ALTER DATABASE [" + target.getTargetDbName() + "] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
with rollback立即使所有连接断开，而不允许它们确保提交。该文档称“所有未完成的事务都将回滚，并且与 AdventureWorks2008R2 示例数据库的任何其他连接将立即断开。” http://msdn.microsoft.com/en-us/library/bb522682.aspx
随着错误和开放连接的消失，服务现在下降得非常快。
We have an app that creates queues and services in SQL Server using the Service Broker to handle db communication. The app uses these services and sends/receives messages correctly, but I now want to test this app's initialization phase (it creates the broker as well as the stored procs that work behind the scenes). Basically, I need to drop the broker elements with some frequency, and its really really slow right now.
I am able to alter the way the app creates the broker elements if that helps - but this question is more related to shutting everything down.
The code I'm using to shut down the broker is:
receive * from [dbo].[notify_initiator_queue]
alter queue [dbo].[notify_initiator_queue] with status = OFF
drop service [//DBNotifyService-Initiator]
drop queue [dbo].[notify_initiator_queue]
drop message types, contacts, etc...
This hangs for some time on 'drop service [//XF/DBNotifyService-Initiator]'. Is there a quicker way to close and drop all or some elements of the service broker?
Thanks!
==Update==
Ok, it took me some time, but the answer below did solve the problem. I wanted to clarify for anyone else that might be having the issue.
My app correctly shut down all the services, queues, contracts, and messages. It was taking forever to shut down the services because there were a bunch of open conversations from a bug in the app. These conversations were being created, used to send a message, and then closed with:
END CONVERSATION @conversation with cleanup
The 'with cleanup' bit closes just the local end of the conversation (think, it allows the server to cleanup any conversations that might have errored out on the other end). It does not close the other end on the sending service, so conversations were being left open. Normal conversations should be ended:
END CONVERSATION @conversation
That fixed the app bug. However, I had several million broken conversations in the db. I could drop the db like a normal person, or I could try to figure out how to close them. To close them all one by one required:
declare @conversation uniqueidentifier
while exists (select top 1 conversation_handle from sys.transmission_queue )
begin
set @conversation = (select top 1 conversation_handle from sys.transmission_queue )
end conversation @conversation with cleanup
end
That takes a few ms per connection (very slow for millions). If I wanted to close them all very quickly, use the answer below and run the modified command:
ALTER DATABASE [" + target.getTargetDbName() + "] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
The with rollback immediate makes all the connections drop without allowing them to ensure a commit. The documentation says 'All incomplete transactions will be rolled back and any other connections to the AdventureWorks2008R2 sample database will be immediately disconnected.' http://msdn.microsoft.com/en-us/library/bb522682.aspx
The services are dropping very quickly now, with the bug and the open connections gone.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
速度慢的原因可能是因为这些元素由于正在使用而被 SCH-S 锁锁定，从而阻止了您的 drop 语句。典型的罪魁祸首是在后台运行的激活程序。通过检查活动监视器阻止原因或查看 < a href="http://msdn.microsoft.com/en-us/library/ms177648.aspx">
sys.dm_exec_requests。可以在sys.dm_broker_activated_tasks中看到正在运行的激活过程。作为解决方法，您可以尝试 ALTER DATABASE SET NEW_BROKER ，它将删除所有现有对话，但不会删除队列/服务/合同/消息类型。它还会更改当前数据库
service_broker_instance_id（如果您在路由中使用它，这一点很重要）。通过删除所有对话，激活的程序应该自行关闭（如果它们编写正确）。但我会推荐一种不同的方法。您应该始终从干净的数据库开始并在干净的数据库上运行部署脚本，而不是让您的测试一次又一次地重用相同的数据库并处理“关闭”阶段的所有错误故障。这样您就不需要“删除”代码。请参阅版本控制和您的数据库。我们使用干净数据库的数据库备份，并始终从该备份开始，恢复它并部署您的应用程序，然后运行验证测试。
The reason is slow is likely because those elements are locked with a SCH-S lock because they are in use, thus preventing your drop statements. The typical culprit is activated procedures that are running in the background. This can be quickly investigated by checking the Activity Monitor blocking reason(s) or by looking in
sys.dm_exec_requests. Running activated procedures can be seen insys.dm_broker_activated_tasks.As a workaround you could try
ALTER DATABASE SET NEW_BROKER, it will drop all existing conversations, but not queue/services/contracts/message types. It will also change the current databaseservice_broker_instance_id(important if you used it in routes). By dropping all conversations the activated procedures should shutdown themselves (if they are correctly written).But I would recommend a different approach. Rather than have your test reuse the same database again and again and handle all the false failures from the 'shutdown' phase, you should always start from a clean db and run your deployment script on a clean db. This way you do not need the 'removal' code. See Version Control and your Database. Us a database backup of a clean DB and always start from that, restore it and deploy your app, then run your validation tests.