Service Broker Information

--: Service Broker Information
--Queue rows and Queue table size
SELECT '--: Service Broker information'
SELECT SCHEMA_NAME(q.schema_id) AS SchemaName, q.name AS QueueName, p.row_count AS QueueRows,i.name as QueueSystemTableName,p.reserved_page_count * 8. / 1024. AS [QueueTableSize(MB)]
FROM sys.service_queues AS q with(nolock)
JOIN sys.objects AS o with(nolock)ON o.object_id = q.object_id
JOIN sys.objects AS i with(nolock)ON i.parent_object_id = q.object_id
LEFT JOIN sys.dm_db_partition_stats AS p with(nolock)ON i.object_id = p.object_id AND p.index_id IN(0, 1)
Order by QueueRows desc, [QueueTableSize(MB)] Desc

--Service Broker size and rows
Select case when o.name = 'sysxmitqueue' then 'transmission_queue' else 'conversation_endpoints' END as ServiceBrokerTable,o.name as SystemTable,
p.reserved_page_count * 8. / 1024. AS [TableSize(MB)],p.row_count as [RowCount]
from sys.objects as o with(nolock)
LEFT JOIN sys.dm_db_partition_stats AS p with(nolock) ON o.object_id = p.object_id AND p.index_id IN(0, 1)
where o.name in ('sysdercv','sysdesend','sysxmitqueue')

--Endpoints rows
Select s.name as [EndPointsSourceServices],e.far_service as [EndPointsTargetServices], state_desc as [EndPointsState],count(*) as [EndPointsCount]
from sys.conversation_endpoints as e with(nolock)
inner join sys.services as s WITH(NOLOCK) on s.service_id = e.service_id
Group by s.name,e.far_service,e.state_desc
Order by [EndPointsCount] desc, far_service,state_desc

--Transmission Queue rows
select from_service_name as [SourceServices],to_service_name as [TargetServices],count(to_service_name) as QueueCount
from sys.transmission_queue with(nolock)
group by from_service_name,to_service_name
Order by QueueCount desc,from_service_name,to_service_name

--Normally the Service Broker numbers should be small, otherwise it indicates a Service Broker issue