SELECT TOP 10
'sys.service_queues',
*
FROM sys.service_queues
-- returns a row for each stored procedure activated by Service Broker. It can be joined to dm_exec_sessions.session_id via the spid column.
SELECT TOP 10
'sys.dm_broker_activated_tasks',
*
FROM sys.dm_broker_activated_tasks
-- returns a row for each Service Broker network connection.
SELECT TOP 10
'sys.dm_broker_connections',
*
FROM sys.dm_broker_connections
-- returns a row for each Service Broker message that an instance of SQL Server is in the process of forwarding.
SELECT TOP 10
*
FROM sys.dm_broker_forwarded_messages
-- returns a row for each queue monitor in the instance. A queue monitor manages activation for a queue.
SELECT TOP 10
'sys.dm_broker_queue_monitors',
*
FROM sys.dm_broker_queue_monitors
-- This catalog view is your first stop when troubleshooting Service Broker. sys.transmission_queue is a crucial catalog view for Service Broker
-- operation because every sent message sits in it until the target sends back a acknowledgement. If an acknowledgement returns successfully
-- then the message will disappear from the view. If not the transmission_status column will hold the error information. If your message
-- doesn't reach its destination look at this catalog view to see what happened.
SELECT TOP 10
'sys.transmission_queue',
*
FROM sys.transmission_queue
-- This catalog view holds a row for each conversation that the Service Broker participates in. The conversation_id column has the same value on
-- both ends. Note here that if you want to close a conversation you have to close it using a conversation_handle value which is different on
-- each conversation end. You have to look it up via the conversation_id value.
-- The state_desc column holds the status of the conversation. If messages can't reach the other end look here so see the state of the conversation
-- which must be 'Conversing' for proper two way functioning.
SELECT TOP 10
'sys.conversation_endpoints',
*
FROM sys.conversation_endpoints
-- This catalog view contains a row for each conversation group. You won't be using this one much since the same information is available in sys.conversation_endpoints
SELECT TOP 10
'sys.conversation_groups',
*
FROM sys.conversation_groups
-- This catalog view contains a row for each remote service binding. Remote service bindings are used for implementing dialog security. This means that you can view
-- which service is bound to which user and which permissions. If you suspect problems with dialog security this is the 2nd place to look in after sys.transmission_queue
-- followed by running Profiler.
SELECT TOP 10
'sys.remote_service_bindings',
*
FROM sys.remote_service_bindings
-- This catalog views contains one row for each created route. Routes are used to locate the network address for a service. Here you search for information about which
-- route deals with which remote Service Broker and the lifetime of the route. Expired routes are hard to debug so check here if you suspect this. Route address can be
-- LOCAL, TRANSPORT or the IP or DNS name of the computer.
SELECT TOP 10
'sys.routes',
*
FROM sys.routes
-- This catalog view contains a row for each contract in the database. Not much other help you can get from this one.
SELECT TOP 10
'sys.service_contracts',
*
FROM sys.service_contracts
-- This catalog view contains a row for each contract-message type pair. This one is more useful than the previous one. If you suspect that the service doesn't support
-- the message type you expect it to support (for example a typo when creating it) or if you set the wrong end of the conversation to use it you can check here if you're right.
SELECT TOP 10
'sys.service_contract_message_usages',
*
FROM sys.service_contract_message_usages
-- This catalog view contains a row for each service-contract pair. That's about it.
SELECT TOP 10
'sys.service_contract_usages',
*
FROM sys.service_contract_usages
-- This catalog view contains a row for each message type registered in the service broker. If you suspect message validation issues this is the place to verify.
SELECT TOP 10
'sys.service_message_types',
*
FROM sys.service_message_types
-- This catalog view returns a row for each reference between service and service queue. A service can only be associated with one queue while a queue can be associated
-- with multiple services. Since one queue can accept messages from more than one service you can check here which services are bound to which queues.
SELECT TOP 10
'sys.service_queue_usages',
*
FROM sys.service_queue_usages
-- This catalog view contains a row for each service in the database. Helps get a clearer view of service ownership and queue use.
SELECT TOP 10
'sys.services',
*
FROM sys.services