I'm having an issue with sql broker service. Is there a reason to worry about setting a new sql broker service? I'm really worried about doing this, but I'm kind of at my wits end since I know it will clear out the message queue to send out, but is this the only thing I need to worry about? Is there anything else that I should be looking for or that has to be done when I do run this statement?

  • 3
    You're asking about a possible fix, but what is your actual root problem? Jan 16, 2014 at 19:17
  • @ThomasStringer I think this is: dba.stackexchange.com/questions/56703/… Jan 16, 2014 at 19:58
  • I'm more concerned with the fact that if I set a new broker, what I have to worry about doing to make sure my instance of service broker is back to how I just had it. Is there a way to batch delete messages out of the sys.conversation_endpoints table? I know about running an infinite while loop, just wondering Jan 16, 2014 at 21:44

1 Answer 1


Only if your database participates in distributed conversations. NEW_BROKER changes your service_broker_id. If you created any route with service name and service instance then it will become invalid as the route service instance value no longer reflect the destination service_broker_guid. It goes w/o saying that doing NEW_BROKER on a database that does have running conversations that span isntances (or even DBs in the same conversation), these conversations will have one half 'nuked' and the other half will be left stranded, unable to make any progress (will have to be cleaned up with END ... WITH CLEANUP or the other database will also have to be NEW_BROKER-ed).

Be aware that NEW_BROKER affects hidden conversations like the ones created by Event Notifications.

If everything you have is local, in the same db, then you have basically nothing to worry about.

  • thanks for the help remus, all of the service broker messages, queues, etc...are in the same db, but we have messages coming from other servers/db's going to service broker and then going to an instance of mongodb, which in turn sends back an id to indicate the message was received...going by the structure that was put around our instance of service broker, i'd have to test in another environment to truly gauge the effects it'll have. Didn't know that setting a new broker actually affected event notifications...something to chew on. Thanks for your help again remus Jan 22, 2014 at 22:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.