Skip to content

Instantly share code, notes, and snippets.

@jdaigle
Created May 9, 2013 15:31
Show Gist options
  • Save jdaigle/5548204 to your computer and use it in GitHub Desktop.
Save jdaigle/5548204 to your computer and use it in GitHub Desktop.
Show Service Bus Statistics
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment