SQL Server Service Broker error database cannot be enabled


By:   |   Updated: 2012-10-24   |   Comments (12)   |   Related: More > Service Broker

Problem

The SQL Server Database Engine returns the following error message after a service broker enabled database is restored to the same SQL Server instance with a different database name and the T-SQL command (ALTER DATABASE 'DatabaseName' SET ENABLE_BROKER) is issued to enable the Service Broker for this restored database.

Msg 9772, Level 16, State 1, Line 1

The Service Broker in database "DatabaseName
" 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.
Solution

This error happens when a Service Broker enabled database is restored or copied to the same SQL Server instance where the original database resides, as a result, both databases end up having the same Service Broker identifier.  When we try to enable Service Broker for the restored database, we will receive this error message. Obviously, this also causes conflicts and existing service broker applications get disturbed.

As you may know, each database on a SQL Server instance contains a Service Broker identifier, which is used by a Service Broker application to uniquely identify the Service Broker instance on the network. This unique identifier also helps the Service Broker application to correctly route and deliver the Service Broker messages to the correct instance of SQL Server. The "service_broker_guid" column of sys.databases catalog view returns this unique identifier value for each database on SQL Server.

For example, when I restored a copy of AdventureWorks2012 database as AdventureWorks2012QA on the same instance where AdventureWorks2012 resides, after the restore both databases end up having the same broker identifier (See below):

USE [master]
GO
SELECT   [name]
 ,[is_broker_enabled] 
 ,[service_broker_guid]
FROM [sys].[databases] 
GO

Figure-1: Both database having same Service Broker identifier

As can be noticed from above, Service Broker is disabled on AdventureWorks2012QA database after the restore.

When I tried to enable enable Service Broker on AdventureWorks2012QA database using "ALTER DATABASE [AdventureWorks2012QA] SET ENABLE_BROKER" T-SQL statement, I get the below error:

Figure-2: Error message

How to fix this issue?

The only way to fix this error is to reinitialize the Service Broker identifier. You can do this as follow:

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

This statement performs the following actions, upon execution:

  • Enables Service Broker for the database
  • Assigns the new Service Broker identifier to the database
  • Clears any existing messages from the Service Broker queue for this database

For example, I executed this statement to enable Service Broker for the AdventureWorks2012QA database as follow:

Figure-3: Enabling Service Broker identifier

After performing this, if you query the sys.databases catalog again, you'll notice that Service Broker is enabled and a new Service Broker identifier is specified for the database. See below for our example:

Figure-4: Verifying Service Broker status and identifier

As you can see from the above, after executing the ALTER DATABASE statement, the new Service Broker identifier is assigned, and Service Broker is enabled for the AdventureWorks2012QA database.

Next Steps
  • To prevent this happening, always ensure that you run the ALTER DATABASE statement on Service Broker enabled database after restoring the database.
  • It is also advisable to verify the Service Broker status after restoring the database, to ensure that Service Broker is enabled.
  • Microsoft Reference: Managing Service Broker Identities.


Last Updated: 2012-10-24


get scripts

next tip button



About the author
MSSQLTips author Basit Farooq Basit Farooq is a Senior Database Administrator and has worked in the IT industry for 11+ years.

View all my tips





Comments For This Article




Tuesday, July 07, 2020 - 8:08:47 AM - Rohit Kumar Kushwah Back To Top (86094)

Thanks, It's work for me. 


Friday, September 21, 2018 - 8:57:50 AM - Marli V Santos Back To Top (77687)

 Thank you for help-me!!!

very good!!!


Wednesday, September 20, 2017 - 11:08:36 AM - hossein Back To Top (66459)

 

thanks alot


Thursday, March 23, 2017 - 4:42:54 AM - Mobin Back To Top (51558)

 

 Thanks For Valuable Info


Wednesday, December 30, 2015 - 8:37:31 AM - Rahmeen Ahmad Khan Back To Top (40328)

 Helpful with clear explanation. Good work!

 


Thursday, August 06, 2015 - 10:28:57 AM - Jeff Back To Top (38394)

Excellent!!!  Thank you very much.


Friday, October 31, 2014 - 2:20:49 PM - Marcel Back To Top (35139)

Thank you!


Monday, August 25, 2014 - 10:07:47 PM - Shady Hozayen Back To Top (34255)

Thanks a lot


Thursday, March 13, 2014 - 11:42:45 AM - javier Back To Top (29744)

Thanks!!


Friday, January 24, 2014 - 6:07:16 AM - satya Back To Top (28214)

 

Thank u


Wednesday, January 30, 2013 - 10:41:53 AM - Pasqui Back To Top (21795)

Thank u, very clear and useful


Wednesday, October 24, 2012 - 7:44:45 AM - Piotr Back To Top (20068)

You can set new_broker option as a part of RESTORE DATABASE



download


Recommended Reading

SQL Server Service Broker example on how to configure, send and receive messages

SQL Server Service Broker Error Receiving Data - 10054 (An existing connection was forcibly closed by the remote host.)

Service Broker Troubleshooting

Event Notifications in SQL Server for Tracking Changes

SQL Server Service Broker Poison Message Handling





get free sql tips
agree to terms


Learn more about SQL Server tools