Enable SQL Server Broker taking too long time

Question

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 1

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;

Answer 2

With below code you can enable the service broker queue immediately

ALTER DATABASE Database_Name SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Answer 3

USE master;
GO
ALTER DATABASE Database_Name
    SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO
USE Database_Name;
GO

Answer 4

Enabling SQL Server Service Broker requires a database lock. Stop the SQL Server Agent and then execute the following:

USE master ;
GO

ALTER DATABASE [MyDatabase] SET ENABLE_BROKER ;
GO

Change [MyDatabase] with the name of your database in question and then start SQL Server Agent.

If you want to see all the databases that have Service Broker enabled or disabled, then query sys.databases, for instance:

SELECT
    name, database_id, is_broker_enabled
FROM sys.databases

Answer 5

In my case, I was using the sql database in my web project like it was in debugging mode. So, When I closed all the connections it executed fine.

Answer 6

Actually I am preferring to use NEW_BROKER ,it is working fine on all cases:

ALTER DATABASE [dbname] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;