Today 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 –
· Connect
SQL server management studio
· Right
click on your database
· Go
to Properties-> Options -> Service
Broker
