First published on TECHNET on Nov 30, 2014
I came across this weird issue where the messages were stuck in the sys.transmission_queue and we do not see the to_broker_instance column for these messages. It was empty, meaning the message was not yet able to identify the routing service at the receiving end.
The rest other sites working meant we had the broker working and also the Conversation Handle was having the correct handles within the SSB_DialogPool. Even the sys.conversation_endpoints show them in CO (Conversing) state. So what’s the reason?
We started the SQL profiler trace only with broker events and the event that took to our notice was –
Broker:Message Classify : "Unable to route the incoming message. The system database MSDB containing routing information is not available. The broker is disabled in MSDB."
So a Broker Classify message is fired when the routing for the message is determined.
In ConfigMgr we do have the Corresponding services at ends but when remote instances are communicating we don’t we use the routes from the local DB but from the MSDB database.
Later found that the customer restored the MSDB and other system databases which might have caused this. So the point is that the SSB needs to be enabled for the MSDB database.
select is_broker_enabled,* from sys.databases
Barring master and model we have all the other databases which have the is_broker_enabled = 1 by default.
Ran below command –
ALTER DATABASE msdb SET ENABLE_BROKER
This failed with the below error –
Msg 9776, Level 16, State 1, Line 1 Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (46BA6922-0A7F-40EF-8201-16F0961A7B6C) does not match the one in sys.databases (5D5541A9-C405-49A2-B01D-08D199ADB116). Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.
Hence used the command to set a NEW_BROKER
ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE
Note: This will kill all the existing conversations in the process in MSDB.
Once done we then saw the message were able to route correctly and the site recovered successfully.
Hope it helps!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.