Enable SQL Server Broker taking too long

Sql Server-2005Service Broker

Sql Server-2005 Problem Overview


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?

Sql Server-2005 Solutions


Solution 1 - Sql Server-2005

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;

Solution 2 - Sql Server-2005

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

Solution 3 - Sql Server-2005

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

Solution 4 - Sql Server-2005

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

ALTER DATABASE [dbname] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionDavid.Chu.caView Question on Stackoverflow
Solution 1 - Sql Server-2005Remus RusanuView Answer on Stackoverflow
Solution 2 - Sql Server-2005IrfanullahView Answer on Stackoverflow
Solution 3 - Sql Server-2005AKDiscerView Answer on Stackoverflow
Solution 4 - Sql Server-2005Mohamad MahmoudView Answer on Stackoverflow