Fix: The Service Broker in database “<DBName>” cannot be enabled because there is already an enabled Service Broker with the same ID.

Scenario:

You backup & restore (or) create a copy of a SQL DB enabled with SQL Service broker under same SQL server instance. Then run ‘ALTER DATABASE SET ENABLE_BROKER’,  you’ll receive the error as given below:

 

Msg 9772, Level 16, State 1, Line 1

The Service Broker in database "<DBName>" cannot be enabled because there is already an enabled Service Broker with the same ID.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

 

Upon checking is_broker_enabled status in sys.databases for the reported DB, you’ll see the results for is_broker_enabled as zero.

 

Query:

SELECT is_broker_enabled FROM sys.databases WHERE name = ‘<DBName>’

 

Result:

is_broker_enabled
0

 

Cause:

Each database contains a unique identifier that is used for routing Service Broker messages to that database. The service_broker_guid column of the sys.databases catalog view shows the Service Broker identifier for each database in the instance.

Because you are restoring a DB with service broker under the same instance, there will be a conflict of service_broker_guid, because of existing service_broker_guid SQL can’t perform Enabling the same service_broker again.

 

SQL Query:

SELECT name, service_broker_guid, is_broker_enabled FROM sys.databases WHERE name like ‘%mydb%’

SQL Results:

name	service_broker_guid	is_broker_enabled
ReportServer$MYDB	5DD7-FD0-4FDA-8213-1DD2F36	1
ReportServer$MYDBTempDB	7AE3A-1734-4765-939E-661C83	1
MYDB	0340-1D65-4250-A4FA-D90825	1
MYDB01	67320072-6BE9-4AC7-ADE5-F034BF50FD3F	1
MYDBSQL	0DE6E238-8F40-4F81-B6ED-A50D0403D4C3	0

 

Reference: Managing Service Broker Identities

Fix:

 

1. Use “ALTER DATABASE <DBName> SET NEW_BROKER”  This will set a new GUID for the DB that you copied/restored
2. Use “ALTER DATABASE <DBName> SET ENABLE_BROKER” This will then enables the new broker

 

 

Reference: How To Enable Service Broker For Specified Database?