Service Broker queue isn't being processed

We were troubleshooting a problem with Service broker today, and realised the queue was being populated but wasn't being processed.

Further investigation revealed that since our database had been migrated from SQL 2000 to SQL 2005 the "is_broker_enabled" was set to "off" and hence the queue wasn't being processed.

To enable it we ran the following command

ALTER DATABASE [databasename] SET ENABLE_BROKER

Now, this command when executed requires the session to have exclusive access to the database, so make sure you either close all other sessions active as well as inactive; or run the command with the termination options:

ROLLBACK AFTER integer [SECONDS] - Specifies whether to roll back after the specified number of seconds.

ROLLBACK IMMEDIATE - Specifies whether to roll back immediately.

NO_WAIT - Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, the request will fail.