/ Database

Enable Service Broker taking forever

Today I had to enable Service Broker in SQL 2008 because when using a SqlCacheDependency I was getting the 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.

This should be pretty easy just using the following command:


The problem I had was that it was taking forever for this to execute. The reason it turns out is that other processes were stopping the script from acquiring an exclusive lock. The solution is pretty simple, a script to kill all other processes. Be aware this will kill all processes if they’re important that could be a very bad thing!

DECLARE @DatabaseName nvarchar(50) 
SET @DatabaseName = N'DatabaseName' 
-- Specify the database we want to run the script against 
DECLARE @SQL varchar(max) 
-- Build a SQL script to kill all other processes 
SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';' 
FROM MASTER..SysProcesses 
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId 
-- Make sure we don't kill our own process 
SELECT @SQL -- Write out the SQL so you can see what's happening 
EXEC(@SQL) -- Kill all the other processes 
-- Now we can enable the service broker instantly 

And Service Broker’s enabled, time for tea.