Subscribe To This Site

MSDB restore error - MSDB restored to different server..Service Broker Error

MSDB restore error - Introduction

The following article details an MSDB restore error I encountered when we were in the process of moving an instance of SQL Server from the physical world to a virtual ESX based server. It was a like for like move, SQL Server 2005 to SQL Server 2005. In order to save ourselves some time and trouble of re-creating the Jobs and schedules we decided to restore MSDB from the old box. Doing this restore did save us the problem of re-creating the jobs but it did create a few other problems one of which I will document here about Service Broker in MSDB and another issue which requires a document all of its own about the SQL Agent not being able to access SQL Server subsystems like SSIS.

Error Message

After the restore we had to restart the new server several times, not because of any SQL issues but for other software requirements. After each restart we got the following error messages in both the SQL Server error log and Event Viewer:

"Server-level event notifications can not be delivered. Either Service Broker is disabled in msdb, or msdb 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."

MSDB restore error - Root 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. Anyway we�d got the error in the log and not liking unexpected errors in the log, and the fact we didn�t get these errors on the old server and we wanted the boxes to be identical we decided to try and resolve the problem.

We 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. The database was restored from a backup of MSDB taken from the old server, where service broker was enabled. Despite there being no obvious reason for the difference in the two database I tried the standard script to enable service broker.

ALTER DATABASE [MSDB] SET ENABLE_BROKER

Executing the above hung and never completed. Some further investigation showed that the process had been blocked.After a quick look around the web it seems that other people have had this issue and it 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.

MSDB restore error - Script to fix it

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] ENABLE_BROKER

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

Conclusion

The MSDB restore error descibed in this artice occured after restoring a backup of MSDB onto a new server. Granted restoring MSDB when migrating servers can save you some time in re-creating jobs. There are some pitfalls to it too. This problem highlighted in this document which produces an error about Service Broker in MSDB and the subsystem issue where the SQL agent can not see certain SQL Server subsystems because the original server stores them in different locations. There will be another article on that later. I guess when moving/migrating instance to instance it is important to bear in mind these issues, when way to avoid these would be to script the jobs from old server and recreate them on the new server, not just taking a backup of the msdb �as is�.

Enter your E-mail Address
Enter your First Name (optional)
Then Don't worry -- your e-mail address is totally secure. I promise to use it only to send you GRE Newsletter.

footer for MSDB restore error page