Enabling Service Broker in SQL Server 2008
In case anyone else is looking for a solution to this problem, the following command worked great for me. It releases all other connections to the database instead of waiting.
ALTER DATABASE [DBNAME] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
In Sql Server 2012 you can go to Properties-> Options -> Service Broker
ok here is how to do this if yours is disabled or you need to restore a backup, which seems to disable it.
just run this script, it will kill all the process's that a database is using (why you carnt in 2008 manually kill process's unlike 2005 is beyond me) and then set the broker
USE mastergoDECLARE @dbname sysnameSET @dbname = 'YourDBName'DECLARE @spid intSELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)WHILE @spid IS NOT NULLBEGINEXECUTE ('KILL ' + @spid)SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spidENDALTER DATABASE @dbname SET ENABLE_BROKER