SQL Server: ALTER DATABASE SET ENABLE_BROKER Taking a Long Time (Fix)

After a recent Availability Group failover, Database Mail stopped sending emails. On investigation, the underlying cause was Service Broker not being enabled on msdb, the system database Database Mail runs out of. Enabling it should be instant. Instead the statement sat there, and this is what it was waiting on.

To confirm the status, I ran:

SELECT name, is_broker_enabled
FROM sys.databases;

The is_broker_enabled column was returning 0, indicating that Service Broker was disabled for that database.

I then attempted to enable it:

ALTER DATABASE [database_name] SET ENABLE_BROKER;

The statement did not error, but it did not complete either. It continued running for several minutes, which is far longer than expected for this change.

This behaviour is not version-specific. Enabling Service Broker requires an exclusive database lock. If there are active connections against the database, SQL Server waits for them to disconnect before applying the change.

The Fix

We can force the change by adding WITH ROLLBACK IMMEDIATE to the ALTER DATABASE command:

ALTER DATABASE [database_name]
SET ENABLE_BROKER
WITH ROLLBACK IMMEDIATE;

With this option added, the command completes immediately and Service Broker is enabled.

That is documented behaviour rather than a workaround. Microsoft Documentation for ALTER DATABASE describes this termination option as immediately disconnecting other sessions and rolling back their incomplete transactions before the change is applied. It is the supported way to take the lock when you cannot wait for it.

There was no need to drop queues, contracts, or services. The delay was simply active connections preventing the required lock.

Do Not Reach for NEW_BROKER

When ENABLE_BROKER appears to hang, the suggestion you will find most often is to use NEW_BROKER instead. It does complete immediately, which is exactly why it looks like the answer. It is not the same operation.

The same ALTER DATABASE reference linked above is explicit about the difference. ENABLE_BROKER starts message delivery and the database keeps its existing Service Broker identifier. NEW_BROKER issues a new identifier, and every existing conversation in the database is removed immediately without end dialog messages being sent. Any route referencing the old identifier has to be re-created.

So on a database with queued conversations, NEW_BROKER throws them away and nothing tells you it happened. It is the right command when you have restored a copy of a database that already exists elsewhere and needs its own identity. It is the wrong command for a database that is simply waiting on a lock.

Confirming Service Broker Status

You can verify the setting at any time:

SELECT name, is_broker_enabled
FROM sys.databases
WHERE name = 'database_name';

A value of 1 indicates Service Broker is enabled for the database.
A value of 0 indicates it is disabled.

Confirm It’s Actually Blocking Before Forcing It

WITH ROLLBACK IMMEDIATE terminates every other connection to the database, worth confirming that’s really what’s happening before reaching for it, rather than assuming. Check what the ALTER DATABASE session is actually waiting on:

SELECT session_id, status, wait_type, wait_time, blocking_session_id, command
FROM sys.dm_exec_requests
WHERE command LIKE 'ALTER DATABASE%';

A blocking_session_id of 0 with a long wait_time and no obvious wait type points somewhere else entirely, not simply “other connections are open.” If it does show active blockers, see Get Blocking Sessions for the fuller picture of who’s holding what before deciding whether to force the change or wait them out.

If ALTER DATABASE SET ENABLE_BROKER appears stuck, it is almost always waiting on active connections.

Use WITH ROLLBACK IMMEDIATE, apply the change, and continue with your configuration.

Why It Broke After a Failover

This is the part worth carrying away, because it applies well beyond Service Broker.

Database Mail runs out of msdb, and msdb is a system database. System databases are not part of an availability group: each replica has its own. So a failover did not break anything. It moved the workload onto a node whose msdb had never been configured the same way as the one it left.

The mail did not stop because of the failover. It stopped because the configuration had been drifting between the replicas for as long as they had existed, and the failover was simply the first time anybody ran that workload on the other node.

The practical consequence: apply this fix on every replica, not just the one you are on. Then check whatever else lives in msdb and has never been tested on the secondary, starting with Agent jobs, operators and alerts.

Frequently Asked Questions