Tuesday, November 26, 2019

ERRORLOG flooded with "Service Broker endpoint" messages.

During system cleanup I hit an issue when SQL Server ERRORLOG file with a size of about 10 GB was 99.9% flooded by messages like this:
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';



2 comments:

  1. Slava,
    Did you see any/many UCS_SESSION_REGISTRATION wait stats when this was happening?

    ReplyDelete
    Replies
    1. That wait is not related to the issue.
      During 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.

      Delete