SQL Server Service Broker cannot be enabled – error

When you try to enable Service Broker using the command:

ALTER DATABASE [Database_name] SET ENABLE_BROKER;

...you may get the following error:

Service_broker_enable_error_same_id

Msg 9772, Level 16, State 1, Line 1
The Service Broker in database "Database_Name" 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.

When a database that has Service Broker enabled is restored or copied to the same SQL Server instance, then both databases have the same Service Broker ID:

SELECT name, is_broker_enabled, service_broker_guid
FROM sys.databases

Service_broker_databases_with_same_id

To avoid the error, you can restore the database using WITH NEW_BROKER option:

RESTORE database AdventureWorks3
FROM DISK = 'D:\Data\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks.bak'
WITH NEW_BROKER,
MOVE 'AdventureWorks2012_Data' TO 'D:\Data\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdvWorks_Data3.mdf',
MOVE 'AdventureWorks2012_Log' TO 'D:\Data\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdvWorks_Log3.ldf'

Or you can set NEW_BROKER on the restored database that has the same Broker ID:

ALTER DATABASE Database_Name SET NEW_BROKER;

If other connections are blocking the SET NEW_BROKER statement in a user database, and the command hangs, you can put the database in single user mode first or use this command:

ALTER DATABASE [Database_name] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

It will close all existing sessions, with rolling back pending transactions and set new Service Broker.

To enable Service Broken on the database:

ALTER DATABASE [Database_name] SET ENABLE_BROKER;

Now, every database has a unique Service Broker ID:

Service_broker_databases_unique_id

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">