SQL Server Performance

disable service broker by default

Discussion in 'SQL Server 2005 General DBA Questions' started by acki4711, Jan 22, 2009.

  1. acki4711 Member

    Hi all,
    If you create a new database the service broker is enabled by default.
    Is there a way to change this behaviour setting service broker to disabled when creating a new db ?
    TIA
    acki
    acki4711, Jan 22, 2009
    #2

  2. moh_hassan20 New Member

    modify model database options, so any new database created will be service broker disabled
    use model
    go

    ALTER DATABASE pubs SET DISABLE_BROKER
    note:
    for any database , it should be in single user mode for enabling /disabling
    -- enable servic broker
    ALTER DATABASE pubs SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
    -- disable
    ALTER DATABASE pubs SET DISABLE_BROKER --WITH ROLLBACK IMMEDIATE
    moh_hassan20, Jan 22, 2009
    #3

  3. acki4711 Member

    Well,
    Thats the funny thing...
    I already disabled service broker on model db but still get a enabled service broker on every newly created db??

    acki4711, Jan 22, 2009
    #4

  4. moh_hassan20 New Member

    it is working in my sql 2008 express. [;)]
    i test it
    when i reviewed database options it is disabld
    moh_hassan20, Jan 22, 2009
    #5

  5. acki4711 Member

    in my sql 2008 it works as well but not in 2005
    acki4711, Jan 22, 2009
    #6

  6. satya Moderator

    As per the BOL:
    Deactivating Service Broker message delivery allows you to safely attach a backup of a database for troubleshooting or data recovery purposes without running the risk of misdirected messages. The is_broker_enabled column of sys.databases shows the current state of Service Broker message delivery for each database.
    SQL Server generates a new service broker identifier for each new database. Because the identifier is new, SQL Server can safely activate Service Broker message delivery in the new database. No other databases on the network should have the same service broker identifier.
    So what you can do is after that CREATE DATABASE statement use ALTER DATABASE :
    <service_broker_option> ::=
    {
    ENABLE_BROKER
    | DISABLE_BROKER
     | NEW_BROKER
    | ERROR_BROKER_CONVERSATIONS
    }
    If in doubt refer to BOL for more information on code examples.
    satya, Jan 23, 2009
    #7

Share This Page