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

Wed Jul 25, 2007 by Mladen Prajdić in sql-server

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!

Legacy Comments


Chris 2009-07-23	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.

Mladen 2009-07-23	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.

Tarun Shah 2009-09-11	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.

pbeast 2010-02-15	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.