Giuseppe Porcelli


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

 





Re: how long need ALTER DATATABLE (DATABASE) SET ENABLE_BROKER ?

DotNetFun


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.





Re: how long need ALTER DATATABLE (DATABASE) SET ENABLE_BROKER ?

Remus Rusanu

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

 

 







Re: how long need ALTER DATATABLE (DATABASE) SET ENABLE_BROKER ?

sergiotarrillo

Holas!

How can I close all the sessions using a query

Saludos,






Re: how long need ALTER DATATABLE (DATABASE) SET ENABLE_BROKER ?

Remus Rusanu

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




Re: how long need ALTER DATATABLE (DATABASE) SET ENABLE_BROKER ?

sergiotarrillo

Thanks Ramus,

Saludos,






Re: how long need ALTER DATATABLE (DATABASE) SET ENABLE_BROKER ?

mojgan behrooz

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

regard you

mojgan