Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Service Broker Troubleshooting


By:   |   Read Comments (5)   |   Related Tips: More > Service Broker

Quickly Resolve Performance Problems for IIS, .NET and SQL Server       >>>   Get Started


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;

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips





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

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

 

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

ALTER QUEUE [dbo].[DeadlockNotificationQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION (  STATUS = ON , PROCEDURE_NAME = [dbo].[ProcessDeadlocksReports] , MAX_QUEUE_READERS = 1 , EXECUTE AS OWNER  ), POISON_MESSAGE_HANDLING (STATUS = ON) 

 

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?

 

Thanks

 

Artem


Wednesday, October 03, 2012 - 10:42:13 AM - smitty11 Back To Top

great tips thanks Jeremy!


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

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

Hi ,

we are facing the below issues in service broker .

error:

 The conversation handle "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" is not found.

how to fix this issue.

 


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

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.


Learn more about SQL Server tools