My Adventures in Coding

November 26, 2007

What to do when a Service Broker Queue Appears Stalled

Filed under: SQL Server 2005 — Brian @ 10:21 am

What happens sometimes with a service broker queue, usually when there has been a db failure or some other issue, is you will have a queue that is enabled and activation is still turned on, but is not processing requests.

So you will try and run the following to fix the issue:

ALTER QUEUE dbo.MyQueue
WITH STATUS = ON , RETENTION = OFF ,
ACTIVATION ( STATUS = ON )

However, this will NOT fix the issue.

The reason is that service broker will see that activation is already turned on for the queue and just do nothing. So what you need to do is first turn activation OFF then turn activation back ON. I know this seems strange, however it seems to be the only way we have been able to restart a queue once we have had a problem occur.

So the fix is to run the following script:

ALTER QUEUE dbo.MyQueue
WITH STATUS = ON , RETENTION = OFF ,
ACTIVATION ( STATUS = OFF )
GO

ALTER QUEUE dbo.MyQueue
WITH STATUS = ON , RETENTION = OFF ,
ACTIVATION ( STATUS = ON )
GO
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: