SQL Server Service Broker error database cannot be enabled

By:Basit FarooqLast Updated: 2012-10-24Comments (11)Related Tips:MoreService Broker

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.

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

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 ofsys.databasescatalog 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):

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:

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

This statement performs the following actions, upon execution:

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

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

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.

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.

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

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*** NOTE *** – If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read theprivacy statementand understand I may unsubscribe at any time.

You can set new_broker option as a part of RESTORE DATABASE

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read theprivacy statementand understand I may unsubscribe at any time.

Copyright (c) 2006-2019Edgewood Solutions, LLCAll rights reserved

Some names and products listed are the registered trademarks of their respective owners.