SQL SERVER – List Service Broker Queue Count

I have not used a service broker for quite a while and I have almost forgotten how it works. However, when paying clients of  Comprehensive Database Performance Health Check ask me questions, I try my best to help if I know the answer. Recently I was asked if I know how to List Service Broker Queue Count.

Yes it is possible to list service broker quite count and there is a very simple script for it.

SELECT ob.Name, p.Rows
FROM sys.objects o
INNER JOIN sys.partitions p ON p.object_id = o.object_id
INNER JOIN sys.objects ob ON o.parent_object_id = ob.object_id
WHERE p.index_id = 1
GO

When you run the script above it will return the result which is very simple to the following the image where it will list the name of the queue with the queue count.

Well, that’s it for today a simple old-style blog which I used too many years ago, and I am still finding the joy in it.

Let me know if you are interested to know more about this topic. I will write more blogs as well as create an SQL in Sixty Seconds video.

Here are my few recent videos and I would like to know what is your feedback about them.

• 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

Reference: Pinal Dave (http://blog.SQLAuthority.com)