1

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

BeginnerDBA
  • 2,169
  • 3
  • 26
  • 49
  • How did you determine that Service Broker was disabled? Individual queues can often get disabled because of poison-message handling, but the only things that should disable the Broker itself are certain DB/Server-level disruptions, such as restoring the database. – RBarryYoung May 30 '18 at 20:14
  • 1
    @RBarryYoung: using SELECT is_broker_enabled,name FROM sys.databases WHERE name = 'DBSERB ' which gave value 0 – BeginnerDBA May 30 '18 at 22:54

1 Answers1

2

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.

Kin Shah
  • 62,225
  • 6
  • 120
  • 236
  • thanks. I have one interesting finding based on what you told me. I have similar set up for this DB in other env's as well. I can see all those env with same db and functionality have one thing in common that is service broker identifier, some 20+ strange alphanumeric values. But the db where its done with new broker has different identifier. Is there a way i can change that to same value others are having – BeginnerDBA May 30 '18 at 19:56
  • 1
    @BeginnerDBA, each database should have a unique SB identifier. It seems the other database may have been restored from backup with the ENABLE_BROKER option instead of NEW_BROKER. That might mot be an issue unless you are using distributed messages. It could also be that one or more individual SB queues were disabled. Check with SELECT name, is_enqueue_enabled FROM sys.service_queues. – Dan Guzman May 30 '18 at 20:13
  • @DanGuzman, I am sure that restore with enable_broker has not been done one these databases on various env. when running your query, it give me 1 that means queues are enabled. 3 are system ones and 2 seems to be user created for these db's – BeginnerDBA May 30 '18 at 20:19
  • @BeginnerDBA, perhaps I misunderstood. I understood the service broker guid was the same in your databases. Verify sys.transmission_queue is empty. – Dan Guzman May 30 '18 at 20:36
  • @BeginnerDBA no you cannot assign / change a service broker identifier. That is done by sql server internally. – Kin Shah May 30 '18 at 21:08
  • @DanGuzman: yes its empty – BeginnerDBA May 30 '18 at 21:12