Service broker doing stuff when I don't want it to RRS feed

  • Question

  • Hello, 

    I have recently implemented a Service Broker solution for asynchronous triggers. It seems to work well, but I have a question. 

    We have "main" database and a "demo" database. The two databases are exactly the same in schema/object definitions, but the main database has "real" data and the demo database just has demonstration data for training and evaluation purposes.

    Only the main database needs Service Broker. The functionality that we have implemented is not applicable to the demo, so Service Broker is disabled in the demo database. 

    When one of the triggers is trigger in the demo database, I would have expected nothing to happen, since SB is disabled. However, rows are being added to the sys.conversation_endpoints and sys.transmission_queue tables. The "transmission_status" states that "The broker is disabled in the sender's database" -- that's expected because it's true. And the state is "CONVERSING" -- this makes me a little nervous as it seems like open conversations are building up. 

    Should I be concerned about this? Should I empty out these tables so they don't build up? Should I try to explicitly end these conversations? And lastly, why is Service Broker still "doing stuff" while it is disabled?

    Thanks for any help you can provide.

    Monday, August 5, 2019 3:38 PM

All replies

  • Service Broker may be disabled but it seems the code is still starting conversations, sending messages, et. al. Rather than fail the commands outright because SB is disabled, SQL Server is kind enough to hold the messages in the transmission queue in cause you later enable SB.

    To prevent messages and conversations from building indefinately, you could create a scheduled job to execute END CONVERSATION...WITH CLEANUP for that broker instance. This is a bit of a hack but will get the job done. IMHO, it would be better to make the trigger code configurable to avoid sending the messages in your demo database or enable SB in the demo database along with functionality that consumes the messages (or a fake service).

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, August 5, 2019 6:07 PM
  • Thanks Dan,

    That makes sense. I think my approach will be to change the code by wrapping the BEGIN DIALOG and SEND statements in an IF statement checking if Service Broker is enabled. Is that similar to what you were suggesting by making the code configurable?


    Monday, August 5, 2019 6:41 PM
  • That would be one way to conditionally executing the code, although I was thinking more along the lines of a conf table. The downside with using the DMVs for that purpose is if SB were to be disabled in the prod database inadvertently (e.g. DBA forgets to enable broker after a restore). But that might be ok depending on the nature of the messages.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, August 5, 2019 6:51 PM