More on SQL Server Service Broker

本文探讨了在实际项目中使用SQL Server Service Broker时遇到的问题及解决方案,包括如何解决服务无法启动的情况,以及如何正确结束对话避免消息残留。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

In earlier posts I already blogged about creating some simple queuing solutions with SQL Server Service Broker. Last week I spend some time actually implementing my research in a customer project and of course ran into two 'interesting' issues.

  1. It is possible for the Service Broker to somehow die on you, whilst appearing to be up and running and with RETENTION=ON sent message appear to be in the queue.

    When using the SQL Profiler it becomes clear that messages don't get processed because the Service Broker is not enabled.

    You can use the following statement to enable Service Broker:

    alter database PFA_DATA set ENABLE_BROKER

    I had to do it on two machines and on of them gave me the following error:

    Msg 9772, Level 16, State 1, Line 2
    The Service Broker in database "PFA_DATA" cannot be enabled because there is already an enabled Service Broker with the same ID.
    Msg 5069, Level 16, State 1, Line 2
    ALTER DATABASE statement failed.

    So instead I used:

    alter database PFA_DATA set NEW_BROKER

    After this the Service Broker was up and running and message started getting delivered.

    If the query takes extremely long to complete restart SQL Server and try again.

    One potential cause for this is when you restore a database.

  2. In my earlier post I just blatantly used WITH CLEANUP to avoid getting EndDialog messages.
    The documentation however states:

    WITH CLEANUP
    Remove all messages and catalog view entries for this side of the conversation without notifying the other side of the conversation. Microsoft SQL Server drops the conversation endpoint, all messages for the conversation in the transmission queue, and all messages for the conversation in the service queue. Use this option to remove conversations which cannot complete normally. For example, if the remote service has been permanently removed, you use WITH CLEANUP to remove conversations to that service.


    So use this sparingly.

    I've discovered a much cleaner way to handle it.
    When sending a message you can use "END CONVERSATION @dialog" to specify that as far as the sender is concerned the dialog is over. In other words: a fire and forget message.
    When the receiving side of the conversation receives the messages and performs its "END CONVERSATION" statement, Service Broker will detect that no acknowledgement (EndDialog) needs to be send.

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值