Enable SQL Server Broker taking too long
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?
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;
USE master;
GO
ALTER DATABASE Database_Name
SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO
USE Database_Name;
GO