How long should I expect ALTER DATABASE … SET ENABLE_BROKER to run?

I’ve seen this question comming back again and again on forums and discussion groups. The ALTER DATABASE … SET ENABLE_BROKER seems to run forever and doesn’t complete. How long should one wait?

This statement completes imeadetly, but the problem is that is requires exclusive access to the database! Any connection that is using this database has a shared lock on it, even when idle, thus blocking the ALTER DATABASE from completing.

There is an easy trick for fix the problem: use the termination options of ALTER DATABASE:        

ROLLBACK AFTER integer [ SECONDS ]         | ROLLBACK IMMEDIATE         | NO_WAIT

-ROLLBACK will close all existing sessions, rolling back any pending transaction.

– NO_WAIT will terminate the ALTER DATABASE statement with error if other connections are blocking it.

ALTER DATABASE [<dbname>] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;