SQL SERVER – List Service Broker Queue Count

I inspect Service Broker Queue Count through queue and internal-table catalogs. A customer requested a quick inventory.

SELECT SCHEMA_NAME(q.schema_id) AS SchemaName,q.name AS QueueName,
       COALESCE(SUM(p.rows),0) AS ApproximateStoredRows
FROM sys.service_queues q
LEFT JOIN sys.internal_tables it ON it.parent_id=q.object_id
    AND it.internal_type_desc=N'QUEUE_MESSAGES'
LEFT JOIN sys.partitions p ON p.object_id=it.object_id AND p.index_id=1
GROUP BY q.schema_id,q.name ORDER BY SchemaName,QueueName;

My original join did not explicitly restrict results to Broker queues. This query starts with sys.service_queues and QUEUE_MESSAGES. It groups partitions and retains queues with no stored rows.

sys.partitions rows is an approximate storage count. Retention and internal queue state affect its meaning. It does not count pending transmissions in sys.transmission_queue. Nor does it establish healthy activation and delivery.

Use the intended database and required metadata permissions. Inspect a particular queue and its retained messages when that is the real question. This inventory changes no Broker settings.

Reference: Service Broker internal queue tables.

Related reading

• Comprehensive Database Performance Health Check
• SQL in Sixty Seconds video
• Bitwise Puzzle – SQL in Sixty Seconds 160
• Find Expensive Queries – SQL in Sixty Seconds #159
• Case-Sensitive Search – SQL in Sixty Seconds #158
• Wait Stats for Performance – SQL in Sixty Seconds #157
• Multiple Backup Copies Stripped – SQL in Sixty Seconds #156

A queue storage count is not an exact deliverable-message count, it is a measure to interpret with queue state.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.