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:
DB1
with logical namesfoo
andfoo_log
with Service Broker enabledDB2
with logical namesfoo
andfoo_log
with 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 .mdf
& .ldf
files.
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?
EDIT
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.