Troubleshooting Tools (Service Broker)
Service Broker provides several tools to diagnose configuration and conversation problems.
Service Broker is a component of the Database Engine. Therefore, many of the Database Engine tools can also be used to diagnose Service Broker problems. For more information, see Troubleshooting Tools (Database Engine).
The ssbdiagnose utility analyzes the configuration of Service Broker services and running conversations for errors. Use ssbdiagnose to do the following:
Confirm that there are no configuration errors in a newly configured Service Broker application.
Confirm that there are no configuration errors after you change the configuration of an existing Service Broker application.
Confirm that there are no configuration errors after a Service Broker database is detached and then reattached to a new instance of the Database Engine.
Determine what errors are preventing messages from being successfully transmitted between services.
For more information about how to use the utility, see ssbdiagnose Utility.
Service Broker messages that are not successfully transmitted to the receiving queue are held in the Service Broker transmission queue in the sending database. You can use the sys.transmission_queue system view in each database to see the messages in the queue. For any messages that are in the queue because of a transmission error, the transmission_status column contains the error message.
Not all messages in the transmission queue are caused by errors:
Some messages in the queue could be in the process of being sent. Examples include waiting for a disconnected receiver to be reconnected or a network send to be acknowledged.
If a queue has RETENTION set to ON, successfully transmitted messages are kept in the transmission queue until the conversation is ended.
The is_conversation_error column indicates whether the message has encountered an error.
For more information about sys.transmission_queue, see sys.transmission_queue (Transact-SQL).
Service Broker provides several SQL Trace events that you can use to diagnose problems. You can use SQL Server Profiler or system stored procedures to configure a SQL Trace that monitors Service Broker events and errors as they occur. This lets you see the Service Broker events that are generated by the system, and whether they encounter errors.
For more information about how to use SQL Trace, see Monitoring Events.
For more information about Service Broker trace events, see Broker Event Category.
Service Broker provides several performance objects that you can use in System Monitor to track the performance of Service Broker operations.
For more information, see Monitoring Resource Usage (System Monitor).
For more information about the Service Broker performance objects, see the following topics:
Service Broker uses the same mechanisms as the Database Engine to return errors to applications or tools. For more information, see Understanding Database Engine Errors.
For information about errors specific to Service Broker, see Errors and Events Reference (Service Broker).
Service Broker errors that are generated when you run a Transact-SQL statement are returned to the calling application. For example, if you are running a script in the Management Studio Transact-SQL Query editor, any errors from Service Broker Transact-SQL statements are returned in the Messages window.
Many Service Broker operations are asynchronous and occur when no application is connected to the instance of the Database Engine. These errors are typically returned in the error log and Windows application log that are associated with the instance of the Database Engine. For more information, see Monitoring the Error Logs.
Service Broker provides several system views to analyze existing Service Broker configurations, or to monitor the state of Service Broker components.
Service Broker Catalog Views
The Service Broker catalog views return data for the current configuration of Service Broker catalog objects, such as queues and contracts.
For a list of Service Broker catalog views, see Service Broker Catalog Views (Transact-SQL).
Service Broker Dynamic Management Views
The Service Broker dynamic management views return data for the current state of Service Broker components, such as activation tasks and Service Broker connections.
For a list of Service Broker dynamic management views, see Service Broker Related Dynamic Management Views (Transact-SQL).