SQl server Service Broker

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.