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?

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.

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.

I have a database that shows is_broker_enabled=0. When I run this:

ALTER DATABASE mynewdatabase SET ENABLE_BROKER with rollback immediate

The Service Broker in database mynewdatabase cannot be enabled because there is already an enabled Service Broker with the same ID.

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.

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

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.

Hi nomiee, how did you rollback? by stopping execution??

Hi nomiee, how did you rollback? by stopping execution??

No, its a command option, as in one of the Posts above: