Make Service Broker Be Less Aggressive About Detecting Poison Messages?

Question

Sql Service Broker uses the following heuristic to determine when you have messages in your queue that are preventing your application from doing any useful work:

"Service Broker provides automatic poison message detection. When a transaction that contains a RECEIVE statement rolls back five times, Service Broker disables all queues that the transaction received messages from, by automatically setting the queue status to OFF." (http://msdn.microsoft.com/en-us/library/ms166137.aspx)

I'm fine with this basic approach, but is there any way to change the # of retries from five to something higher, maybe twenty?

The reason this would be useful to me is that the code I'm currently using to process the queue is an app outside of Sql Server that has around 10 worker threads, each of which has an independent SqlConnection and each of which executes its own independent RECEIVE statements. If this application dies for some reason, this potentially causes a separate rollback transaction for each worker thread, which is enough rollbacks to disable the queue. In contrast, I would like to be able to kill my application without disabling the queue. I should probably rewrite the application to use a single SqlConnection, but it would be a lot easier if I could just say something like

ALTER QUEUE MyQueue SET RollbacksBeforePoison=20

Is anything like that possible?

Answer 1

No, the poison message detection rollback count is hard coded to 5 and you cannot change it. But when a queue is disabled an event notification is raised on the queue for the BROKER_QUEUE_DISABLED event. You can subscribe to this event and have a handler that either notifies an administrator, or even re-enables the queue back. Also the application could probably leverage something like the external activation mechanism to tune its thread pool size to the rate of incoming messages.

Updated

As of SQL Server 2008 R2 there is a new option for ALTER/CREATE QUEUE:

POISON_MESSAGE_HANDLING(STATUS = OFF/ON)

Specifies whether poison message handling is enabled. The default is ON.

A queue that has poison message handling set to OFF will not be disabled after five consecutive transaction rollbacks. This allows for a custom poison message handing system to be defined by the application.