Geek Answers Handbook

To make Service Broker less aggressive about detecting poisonous messages?

Sql Service Broker uses the following heuristics to determine when there are messages in your queue that prevent your application from doing any useful work:

"Service Broker provides automatic detection of poisonous messages. When a transaction containing a RECEIVE statement is returned five times, Service Broker disconnects all the queues from which the transaction was received from messages, 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 a way to change # repetitions from five to something higher, maybe twenty?

The reason I would be useful is because the code I use to process the queue is an application outside of Sql Server, in which there are about 10 worker threads, each of which has an independent SqlConnection and each of which runs it RECEIVE's own independent statements. If this application dies for some reason, it potentially causes a separate rollback transaction for each worker thread, which is enough rollback to turn off the queue. On the contrary, I would like to be able to kill my application without disconnecting the queue. I probably should rewrite the application to use one SqlConnection, but it would be much easier if I could just say something like

ALTER QUEUE MyQueue SET RollbacksBeforePoison=20

Is this possible?

+6
sql-server service-broker
source share
2 answers

No, the number of rejections to detect a poisonous message is hardcoded to 5, and you cannot change it. But when the queue is disabled, an event notification occurs in the queue for the BROKER_QUEUE_DISABLED event. You can subscribe to this event and have a handler that either notifies the administrator or even re-enables the queue. In addition, the application could use something like an external m activation mechanism to adjust its thread pool size to the speed of incoming messages.

Update

SQL Server 2008 R2 has a new option for ALTER/CREATE QUEUE :

 POISON_MESSAGE_HANDLING(STATUS = OFF/ON)

Whether the poisonous message specifies processing is enabled. The default is ON.

The poison message queue setting processing to the OFF position will not be disabled after five consecutive rollbacks of transactions. This allows a conventional poisonous message delivery system to be determined by the application.

+11
source share

Poisoning Detection Algorithm (in Russian) http://www.queue.net.ru/2010/06/poison-message-detection-algorithm.html

0
source share

More articles:


All Articles