Service Broker Troubleshooting

Problem

When learning about Service Broker (or any new SQL Server 2005 technology) you are bound to have some issues with your first application. With the value that Service Broker offers applications directly via the T-SQL language, this is a valuable technology to learn, experiment with and implement. Unfortunately, troubleshooting issues may be a little bit of challenge without knowing where to look and making sure the error messages are properly handled. As such, where can I find information to troubleshoot my Service Broker applications as I learn about the technology?

Solution

Service Broker offers a number of techniques to troubleshoot your Service Broker applications whether they are in development or production stages of the life cycle. In addition, to troubleshooting the application it is also wise to validate the Service Broker ‘infrastructure’ objects are created as expected as well to ensure your application is operating as expected. Below are a set of code, objects and techniques that can be used to troubleshoot your Service Broker applications.

Validate Infrastructure Objects

Service Broker is dependent on five of infrastructure objects in order to operate properly. As such, once you have created your Service Broker objects, it is wise to validate that all of the objects have been created. The queries below would validate that the objects exist. These queries should be issued in both the initiator and target databases to validate that the objects exist in both SQL Server environments.

-- Message Types
SELECT * 
FROM sys.service_message_types; -- Contracts
SELECT * 
FROM sys.service_contracts;
-- Queues
SELECT * 
FROM sys.service_queues;
-- Services
SELECT * 
FROM sys.services;
-- Endpoints
SELECT * 
FROM sys.endpoints;

Troubleshooting the Service Broker Queues

Once you start adding messages to your queues and receiving data from your queues, it is necessary to ensure you are not having any issues with your endpoints, services and contracts. If you are experiencing issues, then this query may identify the conversations that are having issues and additional research may be necessary to troubleshoot the issues further.

SELECT conversation_handle, is_initiator, s.name as 'local service', 
far_service, sc.name 'contract', state_desc
FROM sys.conversation_endpoints ce
LEFT JOIN sys.services s ON ce.service_id = s.service_id
LEFT JOIN sys.service_contracts sc ON ce.service_contract_id = sc.service_contract_id;

Another key queue to keep in mind when troubleshooting Service Broker is the sys.transmission_queue. This is the queue that receives any records that are not written to the user defined queue appropriately. If your overall Service Broker infrastructure is setup properly, then this may be the next logical place to start troubleshooting the issue. You are able to validate the conversation as well as take a peek at the xml (message_body) and find out the error message (transmission_status) for the record.

-- Error messages in the queue
SELECT * 
FROM sys.transmission_queue;

Removing all records from the sys.transmission_queue

Odds are that if your Service Broker infrastructure is setup properly and records are in the sys.transmission_queue, they probably need to be removed to continue to build and test the application. As such, the END CONVERSATION command should be issued with the conversation handle and the ‘WITH CLEANUP’ parameter. Below is an example command:

END CONVERSATION 'conversation handle' WITH CLEANUP;

*** NOTE *** – This command should be issued during development and testing phases of the project and not arbitrarily when the application is in production because the associated data will be lost.

SQL Server Error Log

The next place that should be researched when troubleshooting Service Broker is the SQL Server error log. Some of the issues may not be written to the views above, so the SQL Server error log is another valuable source of information. Below outlines two examples, although based on the issue, the errors could differ:

TRY and CATCH Blocks for Error Handling

Since Service Broker is coded via T-SQL another technique to take advantage of in your code is using the TRY and CATCH block to capture errors and handle them appropriately. Check out the SQL Server 2005 – Try Catch Exception Handling to learn about the coding technique and the associated information that is available from a troubleshooting perspective.

Centralized Error Handling

One approach to improving the TRY and CATCH blocks technique is to use centralized error handling. This can be achieved with the steps outlined in the Standardized Error Handling and Centralized Logging tip. Check it out!

Next Steps

• Although Service Broker is just a portion of the SQL Server relational engine, in some respects it can be considered an application by itself. As such, it certainly needs the time and respect to learn and implement the technology appropriately.
• Service Broker offers a great deal of value and should be a technology that is in your bag of tricks as you adopt SQL Server 2005.
• Although Service Broker is available for usage, determine if Service Broker is the right solution for the problem you are experiencing. The technology offers a great deal of benefits, but based on the situation a better native or third party solution may exist. Please have an open mind.
• As you build your applications, be sure to troubleshoot and validate that records are being written to and received from the queues. The information sources listed in this tip should be checked as well to validate that nothing unexpected is happening as well. For example, make sure you end your conversations appropriately and process those messages types correctly.
• Stay tuned for many more tips on Service Broker. This is just the first tip of many…