Enable SQL Server Broker taking too long

cannot be enabled because there is already an enabled service broker with the same id.
how to check service broker is enabled in sql server
what is the use of service broker in sql server
query to enable service broker in sql server
enable service broker availability group
service broker error
how to check broker sql server
unable to enable service broker

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;

Set enable broker is taking too much time and not getting finished , To enable Service Broker in the msdb database, you will need to stop SQL Database state cannot be changed while other users are using the database  I have run four of these alter database statments to enable service brokers and they have run about 1 hour now. I wonder if this is normal. Does it take long time to alter to enable service broker through a command? I am using 2008 SQL server enterprise evaluation edition on windows. ALTER . DATABASE Content SET ENABLE_BROKER; GO. Thank you for

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

SET ENABLE_BROKER never completes in SQL Server, The Synchronizer will not be able to start if the SQL Server Service Broker is not enabled on both the Catalog as the Customer Database. ASP.NET Forums / Data Access / SQL Server, SQL Server Express, and SQL Compact Edition / Set enable broker is taking too much time and not getting finished ex Set enable broker is taking too much time and not getting finished execution !!

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

ALTER DATABASE [dbname] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

How to enable the SQL Server Broker – InfoBridge, Does it take long time to alter to enable service broker through a command? I am using 2008 SQL server enterprise evaluation edition on  To check if the service broker is enabled execute the following command on the SQL server through Microsoft SQL Server Manager: SELECT is_broker_enabled FROM sys.databases WHERE name = ' [CATALOG NAME]'; SELECT is_broker_enabled FROM sys.databases WHERE name = ' [CUSTOMER DB NAME]'; Replace the [CATALOG NAME] and [CUSTOMER DB NAME] with the correct values you entered previously in the configurator.

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

Alter Database to enable Service Broker runs long - MSDN, This helped. I used 'with rollback immediate' as it was taking too long without it. Proposed as answer by  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.

How to enable service broker for specified database?, Instead switch the database into single user mode, enable the broker service, and restore the database to multi-user mode. 1) Set the database to  To enable Service Broker in the msdb database, you will need to stop SQL Server Agent to release the locks. When you try to enable Service Broker with: if other connections are blocking the statement it will terminate with error: ALTER DATABASE statement failed.

Enabling Sql Server Broker Service Hangs, You can also configure your SQL Server to have all new databases We take a backup and restore it on the same instance as a copy with a  ALTER DATABASE test SET ENABLE_BROKER ALTER DATABASE test SET DISABLE_BROKER This temporarily shuts down the Service Broker operations in the database - you can enable and disable it as often as you want whether there are service broker objects in the database or not.

Service Broker Demystified, 1) SQL Server Service Broker is enabled per database , so If you need to enable Service Broker on multiple databases , some sort of dynamic  Next steps. APPLIES TO: SQL Server Azure SQL Database (Managed Instance only) Azure Synapse Analytics (SQL DW) Parallel Data Warehouse. SQL Server Service Broker provide native support for messaging and queuing in the SQL Server Database Engine and Azure SQL Database Managed Instance. Developers can easily create sophisticated applications that use the Database Engine components to communicate between disparate databases, and build distributed and reliable applications.

Comments
  • As a side note for others, if there are any open connections to the db, they would cause this delay. Run sp_who to show if there are any.. (it might be you)
  • what is the effect of "with rollback immediate"?
  • @A.Dara You cannot alter a database as long as there are transactions pending. Rollback immediate will terminate all of these.
  • you should explain why you prefer it and what it does differently....
  • It is working fine and without take a time in alll cases