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?
SQL Server – Understanding ALTER DATABASE SET NEW_BROKER
sql server
Best Answer
Only if your database participates in distributed conversations.
NEW_BROKERchanges yourservice_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 destinationservice_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.