SQL Service Broker and MSDB wrt ConfigMgr DRS

Hi Folks,

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 J

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


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!

Umair Khan
Support Escalation Engineer | Microsoft System Center Configuration Manager 

Disclaimer: This posting is provided "AS IS" with no warranties and confers no rights.

Comments (2)
  1. Satyabrata B says:

    Dear Umair ,

    we are getting configMgr 2012: DRS and SQL service broker certificate issues for all Parent site and if I try to ran the Replication Link Analyzer it shows sql server Broker login is missing for site (names shows as per my sites) and ask to Recreate sql serverBroker Login.

    Please request to provide the solution on this.

    satyabrata B

  2. Satyabrata B says:

    any update please Umair Khan

Comments are closed.

Skip to main content