1

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.
4
  • @@Barry share the TSQL statements that you are using to enable service broker?
    – AA.SC
    Feb 13, 2015 at 9:54
  • 1
    If you look at the error, it clearly indicates you that you are trying to enable Service Broker against a 'Service_Broker_Guid' which is already been used by some other database, there you need to run Step 2 of my answer.
    – AA.SC
    Feb 13, 2015 at 12:09
  • This has nothing to do with the logical file names... Feb 13, 2015 at 15:35
  • @AaronBertrand Yes I realise that now. It led me down the garden path... Feb 13, 2015 at 16:13

1 Answer 1

3

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
2
  • Perfect. I wasn't aware of NEW_BROKER. That makes sense to me now. Feb 13, 2015 at 12:22
  • 1
    No need to switch to single user. You can run directly ALTER DATABASE DB2 SET NEW_BROKER WITH ROLLBACK IMMEDIATE;. Feb 13, 2015 at 22:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.