More on SQL Server Service Broker

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.