Upgrade Fails - Service Broker can not be enabled

Owned by Mont Rothstein
Last updated: May 16, 2024 by Diane Hancock
1 min read

The Service Broker in database "ForayAdams" cannot be enabled because there is already an enabled Service Broker with the same ID.
ALTER DATABASE statement failed.

An upgrade (at v7.6) fails with a message similar to the one above or Adams Web pointing to a copied database reports The SQL Server Service Broker for the current database is not enabled and trying to enable the Service Broker produces this error.

This error occurs when a database gets two service brokers, one enabled and one disabled or two databases have the same service broker id. One way this can occur is due to a restore.

To fix this run the following SQL.

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

This statement will:

  • 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

You can then re-run the upgrade.

© 2023 Foray, LLC - All Rights Reserved