I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server 2005: How to successfully shoot yourself in the foot with Service Broker

Let me first say that I like Service Broker. I think it's a great addition to SQL Server.

I've explained a bit about it here.

But if you're not carefull you can get weird things happening to you without apparent reason.

So what happened?

Well first i've changed my receiver stored procedure a bit. This resulted in an error when i tried sending new messages.

And from then on all hell broke loose. My error table grew by the second with new rows all containing the same error message:

The service queue "TargetQueue" is currently disabled

CPU was peaking at 100% , I/O was going crazy and I had no idea what to do.

After a few minutes of thinking i simply deleted the service and the madness stopped.

But why this helped??

Well it helped because there was no more service but that was a brute force fix.

 

The answer to what was going on was pretty simple (isn't it always? :)) in the end: Message poisoning.

If your RECEIVE statement can't process and thus rolls back the transaction returning the message to the queue 5 messages in a row,

the queue gets turned off and you get what is known as message poisoning. Also when this happens Service Broker generates

a Broker:Queue Disabled event.

 

There is no automatic way to handle Message Poisoning after the queue gets turned off so you have to handle this yourself.

You can have a counter of concurrent failed messages and when it reaches say 4 you do some action.

Another way is to either remove the failed item from the queue in your Catch block or simply end the conversation like this:

 

END CONVERSATION '12345678-90ab-cdef-1234-56789abcdef1'
    WITH ERROR = 127 DESCRIPTION = N'Unable to process message.' ;
GO

 

Where the guid is your conversation handle.

 

The moral of the story?

KNOW THY TECH!

 

kick it on DotNetKicks.com
 

Print | posted on Wednesday, July 25, 2007 8:46 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: SQL Server 2005: How to successfully shoot yourself in the foot with Service Broker

I have the code below in my catch block:

IF (XACT_STATE()) = -1
BEGIN

ROLLBACK TRANSACTION

END

IF (XACT_STATE()) = 1
BEGIN

declare @error int, @message nvarchar(4000);

select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE();

end conversation @ConversationID with error = @error description = @message;

commit transaction;

END

Is that sufficient or are you saying that I need to end the conversation before/after the rollback, as well? Thanks.
7/23/2009 7:41 PM | Chris
Gravatar

# re: SQL Server 2005: How to successfully shoot yourself in the foot with Service Broker

you don't need to end the conversation. however this is totaly up to you.
7/23/2009 8:56 PM | Mladen
Gravatar

# re: SQL Server 2005: How to successfully shoot yourself in the foot with Service Broker

Hi.

I ran into same error you have described above. I removed all the messges from the service queue. Now when I try to enable the queue the query result windows says "command completed successfully" however, sql server logs says "the queue is currently disabled". It seems I can't enable the queue after several unsuccessful attempts. What should I do now?

Any suggestions will be appreciated.

Regards,

Tarun.
9/11/2009 5:37 PM | Tarun Shah
Gravatar

# re: SQL Server 2005: How to successfully shoot yourself in the foot with Service Broker

nice post. we had a similar problem. haven't applied a fix yet, but this seems to give us some direction. thanks.
2/15/2010 12:35 PM | pbeast
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET