Description
This alert will count the rows in any queue tables and alert you when they are high. If they are high, this could indicate tasks are not getting processed in a timely fashion or processed at all.
Alert Definition
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:
Alert Name: Monitor Service Broker Queues
Execution Interval: 10 Minutes
Notification Text: If you are getting this alert, the Service Broker queue listed below is filling up.
SQL Script:
SET NOCOUNT ON
-- drop temp table if exists and recreate
IF OBJECT_ID('tempdb..#QueueTableInfo') IS NOT NULL
DROP TABLE #QueueTableInfo;
CREATE TABLE #QueueTableInfo (db_name sysname, queue_name sysname, row_count bigint);
DECLARE @SQL varchar(1000), @db_name sysname;
-- loop through non-system databases only
DECLARE c_db CURSOR FOR
SELECT name
FROM sys.databases
WHERE database_id > 4; --might need to exclude more dbs
OPEN c_db;
FETCH c_db INTO @db_name;
@Fetch_Status = 0
BEGIN
-- insert the queue table info for each database into the temp table
SET @SQL = 'INSERT INTO #QueueTableInfo (db_name, queue_name, row_count)
SELECT '''+@db_name+';'', q.Name, p.Rows
FROM '+@db_name+';.sys.objects o
INNER JOIN '+@db_name+';.sys.partitions p ON p.object_id = o.object_id
INNER JOIN '+@db_name+';.sys.objects q ON q.object_id = o.parent_object_id
WHERE p.index_id = 1';
EXEC (@SQL);
FETCH c_db INTO @db_name;
END;
CLOSE c_db;
DEALLOCATE c_db;
-- return the results to DPA alert
SELECT db_name + '..' + queue_name AS queue_name, row_count
FROM #QueueTableInfo;
DROP TABLE #QueueTableInfo;
Execute Against: Monitored Instance
Units: Rows in Queue Table
High Threshold: Min 20, Max empty
Medium Threshold: Min 1, Max 10
