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.
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
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:
SELECT * FROM GetTransmissionQueueCount() GO
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)
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.