By: Jeremy Kadlec | Comments (6) | Related: > Service Broker
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 -- Queues -- Services -- 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; |
One final item is to query the queues that you have created to validate data is being written to or received from the queues. Depending on the application, records should be added and removed from your user defined queues by default. Be sure to issue simple SELECT * FROM statements against your queues for validation of the overall process.
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:
| Date 1/1/2007 00:00:00 AM Log SQL Server (Current - 1/1/2007 00:00:00 AM Source spid62 Message Service Broker needs to access the master key in the database 'YourDatabaseName'. Error code:25. The master key has to exist and the service master key encryption is required Date 1/1/2007 00:00:00 AM Message The Service Broker protocol transport is disabled or not configured. |
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...
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
