Service Broker Poison Message Handling

March 23, 2009

In the recent installments of our series dedicated to topics related to SQL Server 2005 Express Edition, we have been discussing Service Broker features, focusing in particular on error handling techniques that leverage its transactional nature. As we have demonstrated, it is possible to build a certain degree of resiliency into our applications by modifying their code to address different types of obstacles that might interrupt their execution. However, it is also important to realize that there exists an internal Service Broker mechanism, known as Poison Message detection, which provides similar functionality. The purpose of this article is to describe its characteristics and present an example demonstrating its use.

In general, this mechanism comes into play when a message arriving in a queue cannot be properly processed, causing multiple rollbacks of the transaction responsible for its retrieval. Assuming that a condition that originally triggered the failure has not changed, each consecutive attempt to perform the same sequence of steps will result in an identical outcome. In order to prevent this situation from continuing in an uncontrolled manner (unnecessarily contributing to increased utilization of system resources), five consecutive rollbacks automatically cause the queue to become disabled (i.e. its STATUS changes to OFF). While this rather drastic move ensures that ordered message processing is preserved (which is one of the fundamental principles of Service Broker), it also halts all conversation utilizing the same queue. Due to these potentially significant implications, you should consider default poison message detection behavior to be the last resort in your error handling planning, applicable only in cases where custom error detection methods incorporated into your application turn out to be insufficient.

However, regardless of your intentions, you might encounter a situation, in which a queue processing incoming messages becomes a victim of the poison message mechanism. If this happens, the remediation process begins typically with turning off the ACTIVATION feature of the queue containing an offending entry:

ALTER QUEUE qRecv WITH ACTIVATION (STATUS = OFF)

This step prevents automatic invocation of the activation stored procedure (which, after the queue got re-enabled, would likely cause another round of rollbacks, triggering poison message detection, and bringing us back to the starting point). Once the activation is turned off, you can enable the queue by executing:

ALTER QUEUE qRecv WITH STATUS = ON

At this point, you are ready to retrieve the message using RECEIVE TOP(1) statement and insert its content into an auxiliary table for auditing purposes. (Alternatively, you can also terminate the conversation with error, returning its number and description to the initiator). Finally, to return to the original state, enable the activation stored procedure by invoking:

ALTER QUEUE qRecv WITH ACTIVATION (STATUS = ON)

Since the resolution described above relies on the manual intervention (and, until it is completed, no conversations via the disabled queue are possible), you might want to consider implementing event notification functionality, alerting you whenever such an occurrence takes place. This capability leverages the event type Broker:Queue Disabled, generated automatically when the Service Broker shuts down the queue due to the poison message detection. The procedure involves creating a queue (called qPMNotify in our example), intended as a destination of notification messages triggered by the event we are interested in and a corresponding service svcPMNotify, utilizing a contract based on the predefined message type [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]. In addition, we also define a new route (rtPMNotify) in the routing table residing in our local database (dbSBEnt01) and pointing to the svcPMNotify service (the keyword LOCAL indicates that its location is within the current instance of SQL Server). Finally, we also employ a CREATE EVENT NOTIFICATION statement in order to associate the monitored event type (BROKER_QUEUE_DISABLED), with the Service Broker queue affected by it (qRecv) and a target service (hosted in the same database), to which resulting notifications should be delivered (svcPMNotify) :

-- on srvEnt01
USE dbSBEnt01
GO

CREATE QUEUE qPMNotify;
GO

CREATE SERVICE svcPMNotify
ON QUEUE qPMNotify
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

CREATE ROUTE rtPMNotify
WITH SERVICE_NAME = 'svcPMNotify',
ADDRESS = 'LOCAL';
GO

CREATE EVENT NOTIFICATION ePMQueueDisabled 
ON QUEUE qRecv
FOR BROKER_QUEUE_DISABLED
TO SERVICE 'svcPMNotify', 'current database'
GO

Obviously, we are not limited to collecting notifications using objects residing in the same database. However, if you decide to use a remote service (and its queue), you will also need to establish full dialog security for Service Broker conversations associated with it. (For more information on this subject, refer to one of our earlier articles) as well as adjust accordingly route definitions (including a route pointing back from a remote system to the local one).

Once these changes are in place, you should be able to verify the resulting functionality by monitoring the content of the sys.event_notifications catalog view (which will include a single row for each ePMQueueDisabled event) as well as qPMNotify queue. XML-formatted body of each of its messages will provide you such information as the event type (in our case, set to BROKER_QUEUE_DISABLED), date and time the event has been generated (PostTime), the process identifier (SPID), security context (LoginName and UserName), or the affected queue (ObjectName). Be aware, however, that errors affecting delivery of event notification messages to the service queue (svcPMNotify) typically result in a spontaneous deletion of the corresponding event notification (which is reflected by an error message in the SQL Server and Windows Application Event logs), so you might want to confirm its operational status by querying sys.event_notification catalog view.

The steps described above give you the ability to capture occurrences of a queue becoming disabled due to the default poison message processing behavior; however, you might also want to be able to implement an additional alerting mechanism that would bring such events to your attention. One way to accomplish this goal is to leverage the queue activation mechanism (which we have described recently) that relies on a stored procedure associated with the notification queue (our qMPNotify) to perform an arbitrary action (such as sending an e-mail message containing notification to a designated distribution list). On a full-fledged instance of the database engine, you can also use a custom SQL Server Agent alert for this purpose; however, this option is not available in the Express Edition.

This article concludes our fairly extensive coverage of Service Broker related functionality available in SQL Server 2005 Express Edition. Note that the majority of topics we have presented here are applicable to the entire product line. In our next article, we will turn our attention to other database administration features.

» See All Articles by Columnist Marcin Policht