Service Broker Troubleshooting

By:   |   Comments (6)   |   Related: > Service Broker


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.

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
FROM sys.service_message_types; -- Contracts
FROM sys.service_contracts;
-- Queues
FROM sys.service_queues;
-- Services
-- Endpoints
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, as 'local service', 
far_service, 'contract', state_desc
FROM sys.conversation_endpoints ce
LEFT JOIN 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
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:

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
Log SQL Server (Current - 1/1/2007 00:00:00 AM
Source spid16
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...

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at 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

Comments For This Article

Thursday, July 20, 2023 - 4:01:48 PM - Ken Back To Top (91414)
I can see that the message is loaded into the Queue but I cannot retrieve it from the TargetService.

Thursday, August 28, 2014 - 4:56:02 PM - Artem Kuznetsov Back To Top (34325)


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 

here is the queue



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?





Wednesday, October 3, 2012 - 10:42:13 AM - smitty11 Back To Top (19778)

great tips thanks Jeremy!

Thursday, September 13, 2012 - 8:59:11 AM - shashank Bhatt Back To Top (19503)

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 'CM_CEN.dbo.ConfigMgrRCMQueue' 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.'



Thursday, July 26, 2012 - 12:55:58 AM - sweety Back To Top (18804)

Hi ,

we are facing the below issues in service broker .


 The conversation handle "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" is not found.

how to fix this issue.


Thursday, May 17, 2012 - 11:52:02 AM - Tony Back To Top (17505)

This message 'The 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!

Tony S.

get free sql tips
agree to terms