Enabling Service Broker in SQL Server

While creating a database for one of our new projects came across with an error:

“The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications”

Now, the question I had in front of me was………

How do I enable Service Broker in SQL server 2008 R2 database? 

I tried executing the below query…..

ALTER DATABASE SET ENABLE_BROKER but it went into an infinite loop.

Again executed the query and got error- ‘database is in use.’

Use Master

ALTER DATABASE [DBNAME] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

And after a long R&D, I killed the entire session id connected to the database, in which I was suppose to enable the broker.

So there are two options to enable the broker.

Option 1-

Just run this script, it will kill all the process's that a database is using and then set the broker

USE master go DECLARE @dbname sysname SET @dbname = 'YourDBName' DECLARE @spid int SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) WHILE @spid IS NOT NULL BEGIN EXECUTE ('KILL ' + @spid) SELECT @spid = min( spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid END ALTER DATABASE @dbname SET ENABLE_BROKER

Option 2 –

1. Connect SQL server management studio

2. Right click on your database

3. Go to Properties -> Options -> Service Broker

About Author:

Raghwendra Mishra is DBA who works as associate consultant with Systems Plus Pvt. Ltd. He in free time reads and write on various web technologies. He can be contacted at: raghwendra.mishra@spluspl.com