Get Transmission queue row count in Service Broker

Get Transmission queue row count in Service Broker

Sometime back I have written one small script (Click here to see that script) which was giving us total number of row in initiator as well as in target queue. It is good to keep watch on the queue count on initiator as well as on target queue but sometime it happens that initiator queue transmits message to the “Transmission Queue” but somehow “Transmission Queue” is not able to pass that message to Target Queue. If we have watch only on Initiator queue and target queue of service broker and don’t keep watch on transmission queue, we might fall under an impression that everything is fine and everything under control but situation might be opposite.

Hope you got an importance of transmission queue by now and wanted to see the TSQL which can get you the total row count (message to send) in transmission queue. Here is the user defined function which I used to use in order to get total row/message count in transmission queue.

[sourcecode language=”sql”]

CREATE FUNCTION GetTransmissionQueueCount()

RETURNS TABLE

AS

RETURN

SELECT

‘Transmission Queue’ AS QueueName,

Parti.Rows

FROM

sys.objects AS Obj

INNER JOIN

sys.partitions AS Parti

ON

Parti.object_id = Obj.object_id

WHERE

Obj.name = ‘sysxmitqueue’

GO

[/sourcecode]

As soon as you have above user defined function ready in the database where you have setup service broker, you can use that function with simple SELECT statement like this:

[sourcecode language=”sql”]

SELECT * FROM GetTransmissionQueueCount()

GO

[/sourcecode]

If there is any message available in transmission queue of database, you will get value or else you will get zero.

I have written some other articles related to service broker, if you are directly landed to this article and new to Service broker, have a look at following articles which covers Service Broker concept from scratch.

  • Importance of Service Broker in SQL Server (Click Here)
  • Introduction of Service Broker in SQL Server (Click Here)
  • Enable Service Broker in SQL Server database (Click Here)
  • Small demonstration of Service broker for beginners (Click Here)
  • Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
  • Find Service Broker Queue Count in SQL Server (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “Get Transmission queue row count in Service Broker”

Comments are closed.