SQL Server Service Broker – Resolving Service Broker Not Receiving Messages

service-brokersql serversql-server-2008-r2

Due to lack of my knowledge in service broker i am finding hard time in troubleshooting Service broker in one of the databases.

  1. Initially the Service broker somehow got disabled on this database running ssis packages using conversations to process messages. Still wandering how it got disabled and not getting enough info to find. please suggest where to look

  2. when i enabled it using ALTER DATABASE DBSERB set NEW_BROKER WITH ROLLBACK IMMEDIATE, it enabled SB but seems to have clear of the messages i see in sys.conversation_endpoints. But the issue is i dont see those messages coming or starting. so i am back to my original issue of service broker not working. How can i troubleshoot and get this working

Adding more info while troubleshooting

select * from sys.conversation_endpoints shows 0 rows while for similar db in diff env i get almost 5-6 rows. Not sure what does that mean

Best Answer

To answer your questions :

  1. Check default trace to see when it got disabled
  2. when you altered the db with NEW_BROKER, it did more damage than you have thought of

    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).

Unfortunately, your best bet is to re-setup service broker from scratch.

Related Question