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.

The Beginning!!!

This is my very first blogging experience and will try to make this a regular rant on my varied experiences with the world of SQL. I consider this to be my way of giving back to the SQL community as I myself have gained a lot from the vast resources of knowledge and personal experiences of SQL guru's and novices alike. So let the quest begin.....