Service Broker Poison Message Handling

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