How to enable, disable and check if Service Broker is enabled on a database

To enable Service Broker run:
ALTER DATABASE [Database_name] SET ENABLE_BROKER;

If the SET ENABLE_BROKER hangs and never completes read the previous article:
SET ENABLE_BROKER never completes in SQL Server

If you get an error when trying to enable Service Broker read the related blog post:
SQL Server Service Broker cannot be enabled – error

To disable Service Broker:
ALTER DATABASE [Database_name] SET DISABLE_BROKER;

To check if Service Broker is enabled on a SQL Server database:
SELECT is_broker_enabled FROM sys.databases WHERE name = 'Database_name';

If the result is ’1′, the Service Broker is enabled.

If the post helped you, please share it:
Pin It

Leave a Reply

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>