Enable SQL Server Broker taking too long

David.Chu.ca picture David.Chu.ca · Jul 7, 2009 · Viewed 138.6k times · Source

I have a Microsoft SQL server 2005 and I tried to enable Broker for my database with those T-SQL:

 SELECT name, is_broker_enabled FROM sys.databases 
 -- checking its status 0 in my case
 ALTER DATABASE myDatabase SET ENABLE_BROKER

The Alter Database takes long time to process. It is now over half hour and it is still running. Not sure if it is waiting for something else or I have to clean up anything first, such as delete all the messages, contract, queue and services under service broker?

Answer

Remus Rusanu picture Remus Rusanu · Jul 7, 2009

http://rusanu.com/2006/01/30/how-long-should-i-expect-alter-databse-set-enable_broker-to-run/

alter database [<dbname>] set enable_broker with rollback immediate;