Sql-server – The Service Broker endpoint is in disabled or stopped state – AoAG – SQL Server 2012 – 15 minute interval

availability-groupsservice-brokersql serversql-server-2012

I receive this error message in SQL Server error log every 15 minutes. I have setup AOAG on this server. The database option "Service Broker – Broker Enabled" is turned on for the Blackberry related databases and MSDB databases on this instance. Blackberry support mentioned they do not need this enabled. I have other AOAG instance and they do not receive this error.

Please share your wisdom in this regard. I have not used service broker yet and did not find a similar post on stack exchange from my search.

Thank you

Best Answer

According to this link,

It’s a default behavior. The message will show when AlwaysOn availability group Listener is created and SQL Server binds the Listener’s IP dynamically. There is no method to suppress the message. These messages are informational message and do not cause any harm and can safely be ignored.

That link also gives a query to see which databases do and do not have brokers enabled:

SELECT is_broker_enabled, name  FROM sys.databases

Related Solutions

Sql-server – SQL Server 2008 R2 Service Broker Troubleshooting

The way you run ssbdiagnose in the post it would connect to the database MyDatabase on localhost and search for the initiator and target service. You mention that you've set up for two different instances, so the correct command line should be something like:

ssbdiagnose CONFIGURATION\
  FROM SERVICE /test/initiator -E -S <instance1> -d <db1>\
  TO SERVICE /test/target -E -S <instance2> -d <db2>

(I used the unix style command line terminators to prettify the code. You should use a single line).

When the connection params are pulled out ahead of the CONFIGURATION keyword they apply to every connection (FROM, TO).

SQL Server Query Notification Errors – Error 9245 Severity 16 State 1

The error 9245 message appears to be directly connected to the 'The query notification dialog on conversation handle...' message in that SQL Server 2012 SP1 appears to only show that error a maximum of 10 times (with the same timestamp) in the error log, the rest are aggregated into a single error message:

During the last time interval 257 query notification errors were suppressed.

Which is preceded by the message:

Error: 9245, Severity: 16, State: 1.

Prior to SQL 2012 I do not believe the error notifications were aggregated at all, and did not result in a severity 16 error.

Best Answer

Related Solutions

Sql-server – SQL Server 2008 R2 Service Broker Troubleshooting

SQL Server Query Notification Errors – Error 9245 Severity 16 State 1

Related Question