The Service Broker endpoint is in disabled or stopped state. |
There were millions of them and every 15 minutes were adding the new ones.
I've searched the internet and got a not proven suggestion to create a Service Broker Endpoint.
Fortunately enough, the organization I'm servicing right now has Microsoft Service support hours assigned to a SQL DBA group. I've opened a ticket and got an answer from Microsoft.
MS representative referred me an online article: Service Broker with Always On Availability Groups (SQL Server) That article clearly states that all machines with Availability Groups must have
the Service Broker endpoint exists and is correctly configured.
So, the solution to stop that avalanche of useless messages will be very simple: just apply following script on a SQL Server, where you have Availability Groups and experiencing the issue:
CREATE ENDPOINT [SSBEndpoint]
STATE = STARTED
AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL )
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS);
|
To prove that action is successful you can run following script:
SELECT name, protocol_desc, type_desc, state_desc, port
FROM sys.tcp_endpoints
WHERE type_desc = 'SERVICE_BROKER';
|
Also you can search in the ERRORLOG file that your Service Broker is Listening and does not produce garbage messages anymore:
EXEC sys.xp_readerrorlog 0, 1, N'listening';
|
Slava,
ReplyDeleteDid you see any/many UCS_SESSION_REGISTRATION wait stats when this was happening?
That wait is not related to the issue.
DeleteDuring the issue that wait has not appeared in top 15.
However, I can see it on secondary instances and it is barely makes to top 10.
Mostly it goes by number of occurrences with extremely low wait time.