Enabling Service Broker

The following T-Sql enables or disabled service broker on SqlServer 2005. The Service Broker is required by .net for SqlCacheDependency support

-- Enable Service Broker:
ALTER DATABASE [Database Name] SET ENABLE_BROKER;

-- Disable Service Broker:
ALTER DATABASE [Database Name] SET DISABLE_BROKER;

Is Service Broker enabled?

To determine whether or not Service Broker is enabled for a particular database, execute the following T-SQL

SELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name';
-- Where 'Database name' is the name of the database you want to query.

is_broker_enabled will be 1 if Service Broker is enabled for the given database, otherwise it'll be 0.

Author Paul Hayman

Paul is the COO of kwiboo ltd and has more than 20 years IT consultancy experience. He has consulted for a number of blue chip companies and has been exposed to the folowing sectors: Utilities, Telecommunications, Insurance, Media, Investment Banking, Leisure, Legal, CRM, Pharmaceuticals, Interactive Gaming, Mobile Communications, Online Services.

Paul is the COO and co-founder of kwiboo (http://www.kwiboo.com/) and is also the creator of GeekZilla.

Comments

Rishi kumkar said:

please tell me i have 2000SQL SERVER i am unable to eable the service broker please tell me

25/May/2008 13:45 PM

Pappu Kangi said:

Service broker is not available in sql server 2000

25/Jun/2008 22:00 PM

kusum singh said:

hi , i m using sql server 2005.

wen i try to enable service broker on my own database it shows me an error :

Msg 1205, Level 13, State 37, Line 1

Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

and system database do not face the same error . please assist.

26/Jan/2009 07:27 AM

wendy said:

thank you very much!

16/Mar/2010 06:12 AM

Tarun Gupta said:

hiii

i m using sql server 2005 and i want to send a message from one database to another database of a same system...

using service broker...

 please tell me

16/Jun/2010 16:14 PM

Dinesh said:

There is no user login but it is not able to make set service broker for MSDB.

Please suggest something.

26/Sep/2012 08:24 AM

GeekZilla