Show Service Bus Statistics

gistfile1.sql

SELECT
    q.name AS Queue_Name
  , s.name AS Service_Name
  , p.rows AS Row_Count
  , (p.rows / 2) AS Estimated_Message_Count -- divide by two because our framework always sends an "END CONVERSATION" message with each real message sent
  , COALESCE(f.Failed_Message_Count,0) AS Failed_Message_Count
  -- other interesting things
  --, q.is_enqueue_enabled
  --, q.is_receive_enabled
  --, q.is_retention_enabled
  --, q.is_poison_message_handling_enabled
  --, q.is_activation_enabled
  --, q.is_activation_enabled
  --, q.activation_procedure
  --, q.max_readers
  --, q.execute_as_principal_id
FROM sys.partitions p
  INNER JOIN sys.internal_tables t ON t.object_id = p.object_id
	INNER JOIN sys.service_queues q ON q.object_id = t.parent_object_id
    LEFT JOIN sys.services s ON s.service_queue_id = q.object_id
    LEFT JOIN (
        SELECT QueueName, COUNT(*) AS Failed_Message_Count
        FROM FailedMessage WITH (NOLOCK)
        GROUP BY FailedMessage.QueueName
    ) f ON f.QueueName = q.name
WHERE p.index_id IN (1, 0)
ORDER BY q.name