pointers, solutions and scripts for the SQL DBA
not intended to replace msdn, common sense or oxford commas

Thursday, January 30, 2014

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.