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.