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

How to insert Stored Procedure result into a temporary table in SQL Server

SET ENABLE_BROKER never completes in SQL Server

SET ENABLE_BROKER command needs an exclusive lock on the database. This statement completes immediately unless there are open connections to the database that have locked resources in the database. ENABLE_BROKER will wait until the other sessions release their locks. To enable Service Broker in the msdb database, you will need to stop SQL Server Agent to release the locks.

ALTER DATABASE [Database_name] SET ENABLE_BROKER;

ALTER DATABASE [Database_name] SET ENABLE_BROKER WITH NO_WAIT;

…if other connections are blocking the statement it will terminate with error:

Database state cannot be changed while other users are using the database AdventureWorks

If other connections are blocking the enable Service Broker statement in a user database, you can put the database in single user mode first or use this command:

ALTER DATABASE [Database_name] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

It will close all existing sessions, with rolling back pending transactions and enable Service Broker:

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

To check if Service Broker is enabled on a database:

SELECT is_broker_enabled FROM sys.databases WHERE name = Database_name;

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

SQL Server Service Broker cannot be enabled error

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

July 26th, 2012 Tags:Service BrokerSQL ServerCategory:SQL Server

How to List all Mirrored SQL Server Databases

How to specify a Port Number to Connect to SQL Server Instance

How install SQL Server 32Bit on Windows 64Bit

Find all Tables with and without Identity column in SQL Server Database

Find all Filtered Indexes in SQL Server Database

Find all Indexes that have Included Columns in SQL Server Database

Find all Primary Key Columns from a SQL Server table

How to get Column names of a Table or a View in SQL Server

Find all Tables with Specified Column Name in a SQL Server Database

Find Tables without Clustered Index and with Primary Key in a SQL Server Database

List Tables with Foreign Key Constraint in a SQL Server Database

List all User Tables With and Without Primary Key Constraint in a SQL Server Database

List all User Tables in a SQL Server Database with no Clustered Index

Find Fill Factors for Indexes in a SQL Server Database