Poison Message in Services Broker

A poison message in Services Broker is message that cannot be processed by your code/activation stored procedure causing your code/activation stored procedure to rollback. In these circumstances the message is returned back to the queue. Unfortunately, these messages will continue to be picked up by your code/activation stored procedure resulting in a rolled back transaction and the message is returned back to queue. In other words, a poison message is an invalid message.

Here are some common scenarios which create poison messages:

·         A message is violating foreign keyunique constraints or check constraints

·         A message trying to insert a NULL values into a column (NOT NULL) that does not accept NULLs

·         A message that attempts to insert an incompatible value into column

·         Any data which causes your activation stored procedure to rollback

Unfortunately there is no built-in mechanism to handle (delete) poison messages. You need to write custom code to manage these records. A simple way to approach this is in your activation stored procedure instead of rolling back the transaction in CATCH block of your TRY...CATCH error handling check the record to see if the rollback was due to a poison message. If yes, then log this message in a dedicated error queue. Then as a portion of your business process review these records to identify all the offending/poison messages which caused the control go to CATCH block.

Another approach is to subscribe to the Broker:Queue Disabled trace event or BROKERQUEUEDISABLED event which gets raised when a queue gets disabled after five consecutive rollbacks. On occurrence of such event, you need to receive each message from the queue. If the message is correct, then rollback the transaction so that it returns back to queue for actual processing or if it is a poison message, log it to an exceptions log for auditing purposes and commit the transaction to remove the poison messages from your queue.