Search code examples
.netservice-broker

Service Broker Queue Custom Reports

we have started research on Service Broker and planning to implement in application. But we are not getting how to display Statistics reports(in UI) containing: 1. No of messages in the queue. 2. No of Messages so for Executed/Completed. 3. No of messages IN Process, Waiting, Failed,etc 4. No of Conversations in Specific period of time,etc etc etc....

Is there any possible way to fetch these information fully/partially

Any help regarding this is highly appreciated.

Thanks in Advance.

Solution

  • Use Catalog views (msdn). For example:

    Select * From sys.service_queues
Select * From sys.transmission_queue
Select * From sys.conversation_groups
Select * From sys.conversation_endpoints

    Use DMV (msdn). For example:

    SELECT spid, DB_NAME(database_id) database_name, OBJECT_NAME(queue_id) queue_name,
    procedure_name, execute_as
FROM sys.dm_broker_activated_tasks
WHERE database_id = DB_ID();

SELECT DB_NAME(database_id) database_name, OBJECT_NAME(queue_id) queue_name,
    state, last_empty_rowset_time, last_activated_time, tasks_waiting
FROM sys.dm_broker_queue_monitors
WHERE database_id = DB_ID();

    Use SQL Server Log. Can do also like this:

    EXEC sys.xp_readerrorlog 0, 1

    Use reports in Management Studio. Go to "Service Broker" node, right click and then "Reports-> Standart Reports-> Service Broker Statistics"