How to Troubleshoot Service Broker Problems
In my first and second article about Service Broker I've shown how to build a central data repository on one server with one database that stores data (fist article) and across multiple servers with a single server that stores all data (second article). In this one I'll discuss some of the problems that can arise and how to troubleshoot them.
Tools at our disposal
Profiler
Profiler now has a whole section of events dedicated just to Service Broker. Note that Service Broker conversations are always done between two points. This means that you have to monitor both points with profiler to get the whole accurate picture of what is happening.
These events are: • Broker:Activation fires when a queue monitor starts an activation stored procedure. • Broker:Connection reports the status of a transport connection managed by Service Broker. • Broker:Conversation reports the progress of a conversation. • Broker:Conversation Group fires when a conversation group is created or dropped. • Broker:Corrupted Message fires when a corrupt message is received. • Broker:Forwarded Message Dropped fires when a message meant for forwarding was dropped. • Broker:Forwarded Message Sent fires when a message is successfully forwarded. • Broker:Message Classify fires when routing for a message has been determined. • Broker:Message Undeliverable fires when a received message that should have been delivered to a service in this instance can't be retained. • Broker:Queue Disabled fires when message poisoning was detected. This means there were five consecutive transaction rollbacks on a Service Broker queue. It contains the database ID and queue ID of the queue that contains the poison message. • Broker:Remote Message Acknowledgement fires when a message acknowledgement is sent or received. • Broker:Transmission fires when a transport error occurs in the transport layer. The error number and state values indicate the source of the
Tools at our disposal
Profiler
Profiler now has a whole section of events dedicated just to Service Broker. Note that Service Broker conversations are always done between two points. This means that you have to monitor both points with profiler to get the whole accurate picture of what is happening.
These events are: • Broker:Activation fires when a queue monitor starts an activation stored procedure. • Broker:Connection reports the status of a transport connection managed by Service Broker. • Broker:Conversation reports the progress of a conversation. • Broker:Conversation Group fires when a conversation group is created or dropped. • Broker:Corrupted Message fires when a corrupt message is received. • Broker:Forwarded Message Dropped fires when a message meant for forwarding was dropped. • Broker:Forwarded Message Sent fires when a message is successfully forwarded. • Broker:Message Classify fires when routing for a message has been determined. • Broker:Message Undeliverable fires when a received message that should have been delivered to a service in this instance can't be retained. • Broker:Queue Disabled fires when message poisoning was detected. This means there were five consecutive transaction rollbacks on a Service Broker queue. It contains the database ID and queue ID of the queue that contains the poison message. • Broker:Remote Message Acknowledgement fires when a message acknowledgement is sent or received. • Broker:Transmission fires when a transport error occurs in the transport layer. The error number and state values indicate the source of the