Troubleshooting: The Service Broker in database AED XXX cannot be enabled because there is already an enabled Service Broker with the same ID

From AutoCount Resource Center

Question : The SQL Server Database Engine returned the following error message after user tried to enable service broker: The Service Broker in database “AED_XXX” cannot be enabled because there is already an enabled Service Broker with the same ID.

Version : 1.8 / 1.9 / 2.0


Sv broker1.png


Sv broker2.png


Possible Reason :

This happens when a database is restored to the same SQL Server instance where the original database occupies. Therefore, it will cause both databases to have a same Service Broker identifier.


Solution :

SQL statement to Check Service Broker Identifier

use master
GO
select name,is_broker_enabled,service_broker_guid
from sys.databases
GO


Sv broker3.png

Result shows these 2 databases are having a same broker identifier.


SQL statement to reinitialize the Service Broker identifier and Enable the Service Broker

use master
GO
ALTER DATABASE [DatabaseName] SET NEW_BROKER
GO


Result :

Sv broker4.png


Check again Server Broker Identifier, now a new Service Broker identifier is assigned to the database and server broker is enabled now.

Sv broker5.png


Note : Please seek assistance from your software dealer if the problem still persists after performing the suggested steps.


By : John 200811, Lay Swan 200823, P200824



Go to menu

IconAcc20.PNG AutoCount Accounting 2.0 & 2.1


IconAcc188.PNG AutoCount Accounting 1.8 / 1.9
Wiki-Payroll.png AutoCount Payroll
Wiki-POS.PNG AutoCount POS


Wiki-AOTG.PNG AutoCount On The Go
Wiki-Accounting-Plugin.png AutoCount Accounting Plug-In Documentations