More on SQL Server Service Broker

Source: Internet
Author: User

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 policying 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 messaging side of the conversation parameter es the messages and performs its "end conversation" Statement, service broker will detect that no acknowledgement (enddialog) needs to be send.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.