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

To enable Service Broker run

ALTER DATABASE [DatabaseName] SET ENABLE_BROKER;

If the SET ENABLE_BROKER hangs and never completes

ALTER DATABASE [DatabaseName] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

If you get an error when trying to enable Service Broker

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

ALTER DATABASE statement failed.

ALTER DATABASE [Database_name] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

To disable Service Broker

ALTER DATABASE [DatabaseName] SET DISABLE_BROKER;

To check if Service Broker is enabled on a SQL Server database

SELECT is_broker_enabled FROM sys.databases WHERE name = 'DatabaseName';