Blog Post

It May Be Necessary Re-enable Service Broker After Baremetal Restore

,

After the restore of a database, or perhaps a bare-metal restore of a Windows server running SQL Server, unique IDs in the Service Broker settings may have changed, causing the broker to fail when starting. So, you will need to reactivate Service Broker.

You may first notice this because Database Mail gives you an error message about the Service Broker not being enabled in the MSDB database.

For example:
"...Either Service Broker is disabled in msdb, or msdb failed to start. ... Bring msdb online, or enable Service Broker."

Clicking OK to this prompt will cause SQL Server Management Studio to hang or freeze up. You need to do this in a T-SQL script.

Similarly,

ALTER DATABASE MSDB SET ENABLE_BROKER

will never complete.

It may be necessary to run

ALTER DATABASE MSDB SET NEW_BROKER WITH ROLLBACK IMMEDIATE

to reset the service broker's unique ID on MSDB and then

ALTER DATABASE MSDB SET ENABLE_BROKER

will complete successfully.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating