By:Jeremy KadlecLast Updated: 2007-03-12Comments (5)

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?

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.

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.

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.

Another key queue to keep in mind when troubleshooting Service Broker is thesys.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.

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 thesys.transmission_queue, they probably need to be removed to continue to build and test the application. As such, theEND CONVERSATIONcommand should be issued with the conversation handle and the WITH CLEANUP parameter. Below is an example command:

*** 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.

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:

Log SQL Server (Current – 1/1/2007 00:00:00 AM

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

Log SQL Server (Current – 1/1/2007 00:00:00 AM

Message The Service Broker protocol transport is disabled or not configured.

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 theSQL Server 2005 – Try Catch Exception Handlingto learn about the coding technique and the associated information that is available from a troubleshooting perspective.

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 theStandardized Error Handling and Centralized Loggingtip. Check it out!

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…

Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an co-founder and Edgewood Solutions SQL Server Consultant.

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*** NOTE *** – If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read theprivacy statementand understand I may unsubscribe at any time.

Hi, maybe someone can shine some light on this for me

I had a deadlock monitoring in place and was working great all of the suden the queue disables itsef


if I change the POISON_MESSAGE_HANDLING (STATUS = ON) to OFF the queue stay on with OFF it disables right away, can someone point me to the right direction?

Hi I am facing below issue. that service broker is not foun the route for given service. Kindly suggest how to remove this service entry from service broker.

The activated proc [dbo].[spRCMActivation] running on queue nfigMgrRCMQueue output the following: ERROR Sending SSB Message from: ConfigMgrRCM_SiteCEN to ConfigMgrRCM_SiteBAH SQL Error: 50000 SQL Message: ERROR 50000, Level 16, State 1, Procedure spGetSSBDialogHandle, Line 58, Message: Route is not defined for target site with service name ConfigMgrRCM_SiteBAH.

we are facing the below issues in service broker .

The conversation handle xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx is not found.

This messageThe Service Broker protocol transport is disabled or not configured. also appears if you have Service Broker configured but there are no endpoints configured through which communication can be established!

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read theprivacy statementand understand I may unsubscribe at any time.

Copyright (c) 2006-2019Edgewood Solutions, LLCAll rights reserved

Some names and products listed are the registered trademarks of their respective owners.