Msg 9772, Level 16, State 1 - The Service Broker in database "xxxx" cannot be enabled because there is already an enabled Service Broker with the same ID.

22 November,2018 by Jack Vamvas

Question: I'm attempting to execute the following ALTER DATABASE statement to enable BROKER on a database we've just restored.

ALTER DATABASE [xxxxx] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

and getting this SQL error message

 

Msg 9772, Level 16, State 1, Line 5

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

 

How can I fix this problem and progress with enabling Service Broker? I've restored other databases on the same sql server without a problem . The database is restored successfully and connections are all OK

 

Answer:This error occurs when you are trying to restore the service broker enabled db to the original server , thereofre the databases have the same Broker identifier.
Every db has a unique identifier for Service Broker , which is essential for message routing - and you can explore this further through the service_broker_id in sys.databases.

To fix this issue - you'll need to run a preliminary ALTER DATABASE statement to set a new broker id.

 

USE [master]
GO
ALTER DATABASE [xxxxx] SET NEW_BROKER
GO

 
If you're moving multiple databases between different servers and the databases are utilizing Service Broker  - you'll need to be aware and make sure you add this preliminary step after you've restored but before enabling service broker for the sql server database.

 

Read More SQL Server Service Broker

Change the port for sql server service broker (SQL Server DBA)

Troubleshooting SQL Server Service Broker (SQL Server DBA)

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Msg 9772, Level 16, State 1 - The Service Broker in database "xxxx" cannot be enabled because there is already an enabled Service Broker with the same ID.


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer