Service Broker: Poison Message Handling
-
The following extract is from the "Removing Poison Messages" section of BOL.
"Service Broker provides automatic poison message detection. When a transaction that contains a RECEIVE statement rolls back five times, Service Broker disables all queues that the transaction received messages from, by automatically setting the queue status to OFF. In addition, Service Broker generates an event of type Broker:Queue Disabled.
An administrator may use SQL Server Agent alerts to be notified when a queue is disabled. A developer can also create an application that detects when a queue is disabled by Service Broker. That application often inspects the messages in the queue to find the poison message. Once the application determines which message cannot be processed, the application sets the queue status to ON and ends the conversation for the message with an error. An application that detects poison messages must be careful to clean up any state associated with the conversation when ending the conversation. For more information on creating an application to recover from poison messages, see
Can someone please tell me how on earth you set up an Alert which fires on the Broker:Queue Disabled event?
-
You can configure SQL Agent to fire on alerts, but you'd have to determine the right error message #. I was looking last night but didn't narrow down on exactly which one you need, but you likely could determine it by running Profiler and forcing the queue disable in a test environment.
K. Brian Kelley
@kbriankelley -
I have attempted to use profiler, but it does not seem to raise an error number (btw, where have all the server messages goine in 2005??).
From what I have managed to read up on this subject, it may be possible to create an alert for Broker: Queue Disabled by using the WMI events - but I just can't seem to find a suitable example of how to create a WMI based Alert.
-
I'll experiment over the weekend and post what I find by Monday. Even if I don't hit upon something, hopefully it'll give you some areas of where not to try.
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply