SQL Server Service Broker Poison Message Handling

By: Arshad Ali   |   Updated: 2011-03-16   |   Comments   |   Related: > Service Broker

Problem

Have you ever figured out why your Service Broker queue gets disabled automatically? What causes your Service Broker queue to get disabled in the first place? What is a poison message with respect to Service Broker? Is there anything new in SQL Server 2008 R2 for managing poison messages in Service Broker?

Solution

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

In these scenarios, the message processing code/activation stored procedure would not be able to process the message and message would be returned back to the queue after rolling back the transaction.  Please note, a poison message is not a corrupt message as Service Broker itself does integrity check for detecting message corruption.

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 BROKER_QUEUE_DISABLED 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.

With the release of SQL Server 2008, Service Broker handles poison messages automatically by disabling (status is set to OFF and a trace event of type Broker:Queue Disabled is generated) the queue after five consecutive rollbacks of your code/activation stored procedure while retrieving and processing poison message from the queue.  This is the default behavior and there is no way to disable it.  You can create a SQL Agent Job to run on a predefined schedule to check the status of the queue and notify you if the queue is disabled.  Or you build an application to subscribe to the BROKER_QUEUE_DISABLED event.

Once you have identified the queue is disabled, then you need to identify the messages which cannot be processed (poison messages).  You need to end the conversations that have the poison messages with an error.  This is to notify the sender that the messages of the conversation cannot be processed.  When a conversation is ended, Service Broker discards all the messages belonging to that conversation, so these need to be accounted for as well.

-- Script #1 - Ending conversation handle which contains poison messages
END CONVERSATION {Conversation handle}
WITH ERROR = 127 DESCRIPTION = N'This conversation contains a poison
messages and it was ended without processing the messages.'
GO

With SQL Server 2008 R2, we have additional control in Service Broker to determine if we really want to automatically disable the queue when a poison message is detected i.e. automatic poison message detection.  We now have the ability to disable this automatic poison message detection and write our own custom code to handle the poison messages. In the next script block, you can see the that I am creating a queue which has automatic detection of poison message ON which is the default behavior.

-- Script #2 - Creating queue with automatic poison message detection
-- Creating a queue with enabled status and with automatic poison message detection
CREATE QUEUE {Queue Name}
WITH STATUS = ON,
POISON_MESSAGE_HANDLING (STATUS = ON)
GO

In the next script block, you can see the that I am creating a queue which has automatic detection of poison message OFF, which means Service Broker will not automatically detect poison messages and disable the queue after five consecutive rollbacks. In this case you need to have your own custom application for poison message handling, see the reference below for more information for creating an application for poison message handling.

-- Script #3 - Creating queue without automatic poison message detections
-- Creating a queue with enabled status and without automatic poison message detection
CREATE QUEUE {Queue Name}
WITH STATUS = ON,
POISON_MESSAGE_HANDLING (STATUS = OFF)
GO

In the next script block, you can see that an existing queue can be altered to enable or disable automatic poison message handling too.

-- Script #4 - Creating queue with automatic poison message detection
-- You can enable automatic poison message detection on your existing queue
ALTER QUEUE {Queue Name}
WITH POISON_MESSAGE_HANDLING (STATUS = ON)
GO
-- You can disable automatic poison message detection on your existing queue
ALTER QUEUE {Queue Name}
WITH POISON_MESSAGE_HANDLING (STATUS = OFF)
GO

Next Steps

• Review the Removing Poison Messages article on msdn.
• Review the Handling Poison Messages article on msdn.
• Review the existing Service Broker tips.

About the author

Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips