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
