how long need ALTER DATATABLE (DATABASE) SET ENABLE_BROKER ?

I am try to start with SQL BROKER service,

When I lunch from sql Management studio the following query, this don't finish never.

ALTER DATATABLE dbname SET ENABLE_BROKER

Where I am mistaking ?

I've written some article about SB here: http://www.dotnetfun.com/Articles/sql/sql2005/SQL2005ServiceBrokerProblems.aspx

Essentially:

-- Enable Service Broker: ALTER DATABASE [Database Name] SET ENABLE_BROKER; -- Disable Service Broker: ALTER DATABASE [Database Name] SET DISABLE_BROKER; SELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name'; -- Where 'Database name' is the name of the database you want to query. |||

I assume you mean ALTER DATABASE.

ALTER DATABSE dbname SET ENABLE_BROKER requires an exclusive lock on the database. Any session using that database has a shared lock on it, thus bloking the ALTER. So make sure you close (or switch to another database context) all sessions.

HTH,

~ Remus

|||

Holas!

How can I close all the sessions using a query?

Saludos,

|||You can use the WITH ROLLBACK IMMEDIATE clause of the ALTER DATABASE to force the close off conflicting sessions|||

Thanks Ramus,

Saludos,

|||

thank you for your best guidance that was very useful for me to solve my problem.

regard you

mojgan

|||Hi there,

I faced the same problem you have mentioned.

Then, I found a simple solution. You need to use the following 2 statements together, and the query will finish in no time:

Use NorthWind
alter database NorthWind SET ENABLE_BROKER