SQL Server with Mr. Denny

Nov 6 2008   11:00AM GMT

Very useful little query to monitor the health of service broker

Denny Cherry Denny Cherry Profile: Denny Cherry


While dealing with a SQL Server Service Broker issue I needed a quick easy way to see how backlogged the service broker was.  So I came up with this little query which is surprisingly effective.

select far_service, state_desc, count(*) messages
from sys.conversation_endpoints
group by state_desc, far_service
ORDER BY far_service, state_desc

Hopefuly you find this helpful.


3  Comments on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • 21248772
    I have two question...
    1.i have a DBServer and more DBNodes, DBNode send message to DBServer, the DBServer receive the msg from Node. the Server want to know the msg's owner ip, the best method get the ip from msg..
    2.all Nodes send msg to Server, how to Monitor the Server Performance.

    20 pointsBadges:
  • Denny Cherry
    There is no way to get the IP Address from the system. You would need to include that information within the message before you send it. As for the performance, what are youtrying to monitor, the speed that the messages are received, how fast they are processed? There are some performance monitor counterswhich you can monitor which will give you a lot of this information.
    69,000 pointsBadges:
  • k99ja04
    This script actually counts dialogs not messages.  If your application sends more than one message on a dialog this can be misleading.

    	 SCHEMA_NAME(q.[schema_id])		AS [SchemaName]
    	,q.[name]						AS [QueueName]
    	,p.[rows]						AS [MessageCount]
    FROM sys.service_queues AS q
    	JOIN sys.objects AS o ON o.[object_id] = q.[object_id]
    	JOIN sys.objects AS i ON i.[parent_object_id] = q.[object_id]
    	JOIN sys.partitions p ON p.[object_id] = i.[object_id]
    		AND p.[index_id] IN (0,1)
    WHERE q.[is_ms_shipped] = 0;
    10 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: