jump to navigation

Service Broker queue not being processed September 14, 2008

Posted by Chirag in Service Broker, SQL 2005, SQL 2008, T-SQL.
Tags: , , ,
trackback

We were troubleshooting a problem with Service broker, 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.

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: