How to troubleshoot Service Broker problems

By Mladen Prajdić on 21 August 2007 | Tags: Service Broker

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 error.
Security Audit:Audit Broker Login reports audit messages related to Service Broker transport security.
Security Audit:Audit Broker Conversation reports audit messages related to Service Broker dialog security.

Some of these events have a EventSubClass column that provides more information about the event so be sure to include that column. I usually simply select all of the events and all of the columns. This provides all the info you can get from traces for better analysis.

Catalog Views and Dynamic Management Views

There are 4 DMV's for service broker

sys.dm_broker_activated_tasks returns a row for each stored procedure activated by Service Broker. It can be joined to dm_exec_sessions.session_id via the spid column.
sys.dm_broker_connections returns a row for each Service Broker network connection.
sys.dm_broker_forwarded_messages returns a row for each Service Broker message that an instance of SQL Server is in the process of forwarding.
sys.dm_broker_queue_monitors returns a row for each queue monitor in the instance. A queue monitor manages activation for a queue.

There are 11 Catalog views. These hold all the necessary information to correctly diagnose problems. It's important to know what to search for and where. That's why I'll go over each catalog view and describe in which situation it's useful.

sys.transmission_queue

This catalog view is your first stop when troubleshooting Service Broker. sys.transmission_queue is a crucial catalog view for Service Broker operation because every sent message sits in it until the target sends back a acknowledgement. If an acknowledgement returns successfully then the message will disappear from the view. If not the transmission_status column will hold the error information. If your message doesn't reach its destination look at this catalog view to see what happened.

sys.conversation_endpoints

This catalog view holds a row for each conversation that the Service Broker participates in. The conversation_id column has the same value on both ends. Note here that if you want to close a conversation you have to close it using a conversation_handle value which is different on each conversation end. You have to look it up via the conversation_id value.

The state_desc column holds the status of the conversation. If messages can't reach the other end look here so see the state of the conversation which must be 'Conversing' for proper two way functioning.

sys.conversation_groups

This catalog view contains a row for each conversation group. You won't be using this one much since the same information is available in sys.conversation_endpoints

sys.remote_service_bindings

This catalog view contains a row for each remote service binding. Remote service bindings are used for implementing dialog security. This means that you can view which service is bound to which user and which permissions. If you suspect problems with dialog security this is the 2nd place to look in after sys.transmission_queue followed by running Profiler.

sys.routes

This catalog views contains one row for each created route. Routes are used to locate the network address for a service. Here you search for information about which route deals with which remote Service Broker and the lifetime of the route. Expired routes are hard to debug so check here if you suspect this. Route address can be LOCAL, TRANSPORT or the IP or DNS name of the computer.

sys.service_contracts

This catalog view contains a row for each contract in the database. Not much other help you can get from this one.

sys.service_contract_message_usages

This catalog view contains a row for each contract-message type pair. This one is more useful than the previous one. If you suspect that the service doesn't support the message type you expect it to support (for example a typo when creating it) or if you set the wrong end of the conversation to use it you can check here if you're right.

sys.service_contract_usages

This catalog view contains a row for each service-contract pair. That's about it.

sys.service_message_types

This catalog view contains a row for each message type registered in the service broker. If you suspect message validation issues this is the place to verify.

sys.service_queue_usages

This catalog view returns a row for each reference between service and service queue. A service can only be associated with one queue while a queue can be associated with multiple services. Since one queue can accept messages from more than one service you can check here which services are bound to which queues.

sys.services

This catalog view contains a row for each service in the database. Helps get a clearer view of service ownership and queue use.

How to go about it?

It's important to know how the full conversation between endpoints occurs:

Initiator sends the message.
Target receives the message
Target sends back the acknowledgement of successfull message receive
Initiator accepts the acknowledgement

The two main starting points are sys.transmission_queue and sys.conversation_endpoints. The first one will tell you what errors happened when sending messages. The other will tell you if the conversations you're sending your messages on are valid and are being used properly.

When you suspect a problem, look into these two on the Initiator side. If you don't find anything wrong so far, start up Profiler and attach it to both the Target and Initiator with all of the events for a clearer picture. When debugging send problems catalog views are your friend and when debugging receive problems the profiler is your friend.

Also don't forget to look at your queues if they hold any errors in them. After five failed concurrent transactions you will get what is known as Message Poisoning after which your queue will get disabled and a Broker:Queue Disabled event will be fired. If this happens you will have problems like I did. So be sure to handle this in your activation stored procedure on the target part of the conversation.

Conclusion

Hopefully, I shed some light on what each part of the service broker helper system objects does and what to use on which occasion. It may look hard at the beginning but as you go along you'll see that the same patterns on how to troubleshoot usually repeat themselves.