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:
ALTER DATABASE 'DatabaseName' SET ENABLE_BROKER
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 ALTER DATABASE WBC_web SET ENABLE_BROKER
And Service Broker’s enabled, time for tea.