The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob

  Sunday, July 22, 2018

 


Home
Blog
bob tech
Calendar
Contact Bob
Pictures
VW Buses
HomeBrew
Cook Book

 

EJH the 1st year

  bob Links
AVP-WORLDWIDE.
Filled with mingled cream and amber I will drain that glass again. Such hilarious visions clamber through the chamber of my brain--quaintest thoughts--queerest fancies come to life and fade away: What care I how time advances? I am drinking ale today. - Edgar Allan Poe
Wider View Insert

Cluster
DOS
IBM - AS400
MOM
Performance Counters
Service Broker
     Server-level event n...
SQL Server
Windows OS

Insert Category

Will be added as a sub-category of, Service Broker
Server-level event notifications can not be delivered

This is the resolution to a problem we encountered on the server VsqlSVC1\SVCCTR1

Problem:

After each restart of the SQL Server services we got the following error messages in the SQL Server error log:

Server-level event notifications can not be delivered. Either Service Broker is disabled in msdb, or msdsb failed to start. Event notifications in other databases could be affected as well. Bring msdb online, or enable Service Broker.

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

Cause:

It seems that Service Broker is enabled by default in MSDB and even though we don’t use service broker directly I assume that SQL Server and MSDB do use it for internal processes.

I ran the following query to identify if MSDB had Service Broker enabled

SELECT is_broker_enabled FROM sys.databases WHERE name ='MSDB'

This returned a value of 0. Service Broker was not enabled.

I then ran the following script to enable service broker.

ALTER DATABASE [MSDB] SET ENABLE_BROKER

The script hung and never completed. Some further investigation showed that the process was being blocked by the MSDB database.

It appears that this usually happens when the MSDB database has been restored from another server, like when you move an instance and want to save some time and the trouble of re-creating the Jobs and schedules.

The issue relates to the service_broker_guid being the same for the msdb database on the new instance and the on the old instance. It is necessary for service broker to have a unique GUID. So we need to create a new broker for the MSDB database on the new instance.

Solution:

To create a new broker, and thus a new GUID for the msdb database we run the following script:

ALTER DATABASE [MSDB] SET NEW_BROKER WITH ROLLBACK IMMEDIATE

ALTER DATABASE [MSDB] SET NEW_BROKER

ALTER DATABASE [MSDB] SET ENABLE_BROKER

This created a new service broker guid and enabled service broker on msdb.