Query to Troubleshoot the service broker issues in SQL Server
Service broker is the technology which provides messaging and queueing functions between instances.
Query for information and troubleshooting service broker:
SELECT t1.name AS [Service_Name], t3.name AS [Schema_Name], t2.name AS [Queue_Name],
CASE WHEN t4
.state IS NULL THEN 'Not available' ELSE t4
.state END AS [Queue_State]
, CASE WHEN t4
.tasks_waiting
IS NULL THEN '--' ELSE CONVERT(VARCHAR, t4
.tasks_waiting
) END AS tasks_waiting
, CASE WHEN t4
.last_activated_time
IS NULL THEN '--' ELSE CONVERT(varchar, t4
.last_activated_time
) END AS last_activated_time
, CASE WHEN t4
.last_empty_rowset_time
IS NULL THEN '--' ELSE CONVERT(varchar,t4
.last_empty_rowset_time
) END AS last_empty_rowset_time
, ( SELECT COUNT(*) FROM sys.transmission_queue t6
WHERE (t6
.from_service_name
= t1
.name
) ) AS [Tran_Message_Count]
FROM sys.services t1
INNER JOIN sys.service_queues t2
ON ( t1
.service_queue_id
= t2
.object_id ) INNER JOIN sys.schemas t3
ON ( t2
.schema_id = t3
.schema_id ) LEFT OUTER JOIN sys.dm_broker_queue_monitors t4
ON ( t2
.object_id = t4
.queue_id
AND t4
.database_id
= DB_ID() ) INNER JOIN sys.databases t5
ON ( t5
.database_id
= DB_ID() )
Hope the above helps
Thanks
