SQL Server 2019 how can I create an alert for when service broker messages are aggregating in the queue and not being processed?

Question

How do I create an alert in SQL Agent, to notify me once service broker message queue exceeds 300 messages? I am trying to get a notification to an operator in SQL Agent when this occurs.

Answer 1

based on the documentation here

https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-broker-statistics-object?view=sql-server-ver16

you should use an alarm like this:

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'MyAlert', 
        @enabled=1, 
        @delay_between_responses=0, 
        @include_event_description_in=0, 
        @performance_condition=N'Broker Statistics|Forwarded Pending Msg Count||>|300', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO