A colleague was having trouble enabling the Service Broker for a particular database. SQL Server kept on throwing an error stating that the Service Broker was already running. When checking the database, the option for Service Broker was disabled.
It turns out that the database was created using a backup of another database and the logical names had not been changed. The original database did indeed have the Service Broker enabled.
So in summary we have:
DB1with logical names
foo_logwith Service Broker enabled
DB2with logical names
foo_logwith Service Broker disabled
We were unable to start Service Broker on
DB2 as SQL Server said it was already running. The databases were pointing to different
Why does the Service Broker use the logical names and not the database names? I always thought that the scope of logical name was restricted to a single database?
This is the SQL Statement that was used to enable Service Broker
ALTER DATABASE DB2 SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
And the error is:
Msg 9772, Level 16, State 1, Line 1 The Service Broker in database "DB2" 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.