locked
How to enable service broker for specified database? RRS feed

  • Question

  • Hi All,

    I am newbie in SQL Service broker. As we know, the is_broker_enabled can help us check whether the specified database is enabled for service broker. I would like to know

    <>How can I enable servce broker for specified database if I see the value(is_broker_enabled) is 0?

    <>How can I disable the service broker for specified database is I see the vlaue (is_broker_enabled) is 1? Do I need to dropped all service broker configration in the specified database?

    Thanks in advance.

    Michael

    Thursday, December 21, 2006 5:31 AM

Answers

  • ALTER DATABASE test SET ENABLE_BROKER

     

    ALTER DATABASE test SET DISABLE_BROKER

     

    This temporarily shuts down the Service Broker operations in the database - you can enable and disable it as often as you want whether there are service broker objects in the database or not.

    Thursday, December 21, 2006 7:28 AM

All replies

  • ALTER DATABASE test SET ENABLE_BROKER

     

    ALTER DATABASE test SET DISABLE_BROKER

     

    This temporarily shuts down the Service Broker operations in the database - you can enable and disable it as often as you want whether there are service broker objects in the database or not.

    Thursday, December 21, 2006 7:28 AM
  • I have a database that shows 'is_broker_enabled'=0. When I run this:

     

    ALTER DATABASE mynewdatabase SET ENABLE_BROKER with rollback immediate

     

    I get this:

     

     

    Msg 9772, Level 16, State 1, Line 1

    The Service Broker in database "mynewdatabase" 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.

     

     

    Any clues why this is happening and how I can fix this problem?

     

     

    I did create this database from a backup of another database that had service broker enabled and then renamed this as a new database.
    Wednesday, October 29, 2008 5:24 PM
  • Your database is a file copy or a restore of another database on your system. You cannot have two databases with same service_broker_quid, you must change the guid on one of them by running
    Code Snippet

    ALTER DATABASE [<database that has to change>] SET NEW_BROKER;


    This operation will cleanup all existing conversations and messages, but will not affect any existing queues, services, contracts etc.
    • Proposed as answer by LearnerSql Sunday, October 2, 2011 5:24 AM
    Thursday, October 30, 2008 8:01 AM
  • Thanks Remus. This helped. I used 'with rollback immediate' as it was taking too long without it.

     

    • Proposed as answer by santosh4106 Thursday, April 1, 2010 11:17 AM
    Thursday, October 30, 2008 3:30 PM
  • Hi nomiee, how did you rollback? by stopping execution??

    Friday, October 6, 2017 12:50 PM
  • Hi nomiee, how did you rollback? by stopping execution??

    No, it's a command option, as in one of the Posts above:

    ALTER DATABASE mynewdatabase SET ENABLE_BROKER with rollback immediate


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, October 6, 2017 1:46 PM