>>VIEW ALL POSTS  

SQL Server with Mr. Denny

« Does your SQL Server database improve SharePoint performance?
How to fix when you can’t copy data when RDPed into a server »
Nov 6 2008   11:00AM GMT

Very useful little query to monitor the health of service broker

Denny Cherry Denny Cherry Profile: Denny Cherry

Tags:

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.

Denny

Comment     RSS Feed     Email a friend

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.
    Thanks

    20 pointsBadges:
    report
  • 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:
    report
  • k99ja04
    This script actually counts dialogs not messages.  If your application sends more than one message on a dialog this can be misleading.

    SELECT 
	 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:
    report

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: