SQL Service Broker – Understanding Logical File Names

service-brokersql-server-2008-r2

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 names foo and foo_log with Service Broker enabled
  • DB2 with logical names foo and foo_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.

Best Answer

There are only 4 steps to enable service broker for a Database and non of them use logical file name as parameter

Try this

USE MASTER
GO

IF EXISTS ( SELECT  is_broker_enabled, Service_Broker_Guid,*
            FROM    sys.databases
            WHERE   name = 'DB2'
                    AND is_broker_enabled = 0 ) 
BEGIN                    

    -- Step 1 ---  Rollback running transactions and brings the database in a single user mode.
    ALTER DATABASE DB2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE


    -- Step 2 --- To avoid distributed conversation and OLD database GUID conflicts.
    ALTER DATABASE DB2 SET NEW_BROKER;


    -- Step 3 --- Set the is_broker_enabled flag to true  
    ALTER DATABASE DB2 SET ENABLE_BROKER;


    -- Step 4 --- Brings the database back to Multi user mode and allow shared locks\connections.
    ALTER DATABASE DB2 SET MULTI_USER WITH ROLLBACK IMMEDIATE

END
Related Question