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 poinsoning.
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!
