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;

When you try to enable Service Broker with:

ALTER DATABASE [Database_name] SET ENABLE_BROKER WITH NO_WAIT;

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

Msg 5070, Level 16, State 2, Line 1
Database state cannot be changed while other users are using the database 'AdventureWorks'
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

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.

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

8 comments to SET ENABLE_BROKER never completes in SQL Server

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">