DBA Scripts: Generate Collector Alerts

🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.In: Maintenance & Automation › Collectors & Baselines

Alert on What Your Collectors Find

Your collectors fill tables every few minutes and nobody reads them. Generate-CollectorAlertJob.sql writes one SQL Agent job that checks the latest collector data every 30 minutes, lists every CRITICAL and WARNING finding, and fails on any CRITICAL. Out of the box it tells nobody, so wire it up with Who Gets Told.


Generate-CollectorAlertJob.sql

/*
Script Name : Generate-CollectorAlertJob
Category    : collectors
Purpose     : Generates DDL to create the DBA - Collector Alert SQL Agent job.
              The job queries [DBAMonitor].[collector].* tables, applies threshold
              checks, outputs findings, and RAISERRORs on any CRITICAL result
              (causing the step to fail and triggering Agent notification routing).
              Edit parameters, review output, then run on the target instance.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-generate-collector-alerts/)
Requires    : sysadmin (to run generated DDL); SELECT on [DBAMonitor].[collector].* at job runtime
Notes       : Depends on the individual collector jobs already being installed and running.
              Thresholds match Invoke-CollectorAlert.ps1:
                wait-stats  PAGEIOLATCH_* >40% CRITICAL / >20% WARNING
                wait-stats  RESOURCE_SEMAPHORE >20% CRITICAL / >10% WARNING
                wait-stats  LCK_M_* >30% CRITICAL / >15% WARNING
                blocking    any event in last 2h WARNING; max wait >60s CRITICAL
                tempdb      version_store_mb >10000 CRITICAL / >2000 WARNING
                tempdb      free_mb <100 CRITICAL / <500 WARNING
                db-growth   AT_LIMIT CRITICAL / NEAR_LIMIT WARNING
                vlf-count   >10000 CRITICAL / >1000 WARNING
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

-- ── Parameters ────────────────────────────────────────────────────────────────
DECLARE @TargetDatabase  sysname       = N'DBAMonitor';
DECLARE @JobOwner        sysname       = N'sa';
DECLARE @CategoryName    nvarchar(128) = N'DBA Collectors';
DECLARE @IntervalMinutes int           = 30;
-- ─────────────────────────────────────────────────────────────────────────────

DECLARE @q       nchar(1)      = NCHAR(39);
DECLARE @crlf    nvarchar(2)   = CHAR(13) + CHAR(10);
DECLARE @ddl     nvarchar(max) = N'';
DECLARE @jobName sysname       = N'DBA - Collector Alert';
DECLARE @stepCmd nvarchar(max);

-- ── Step command (| = single-quote placeholder) ────────────────────────────────
SET @stepCmd = REPLACE(
N'SET NOCOUNT ON;
CREATE TABLE #findings (severity nvarchar(10), collector nvarchar(30), check_name nvarchar(60), detail nvarchar(500));

-- ── Wait-stats delta ──────────────────────────────────────────────────────────
DECLARE @ws_t1 datetime2, @ws_t2 datetime2, @ws_start1 datetime2, @ws_start2 datetime2;
DECLARE @ws_total bigint, @ws_pct decimal(5,1), @ws_int nvarchar(80);

SELECT TOP 1 @ws_t2 = collection_time FROM [<<DB>>].[collector].[WaitStats]
WHERE server_name = @@SERVERNAME ORDER BY collection_time DESC;
SELECT TOP 1 @ws_t1 = collection_time FROM [<<DB>>].[collector].[WaitStats]
WHERE server_name = @@SERVERNAME AND collection_time < @ws_t2 ORDER BY collection_time DESC;

IF @ws_t1 IS NOT NULL
BEGIN
    SELECT @ws_start1 = MIN(sqlserver_start_time) FROM [<<DB>>].[collector].[WaitStats]
    WHERE server_name = @@SERVERNAME AND collection_time = @ws_t1;
    SELECT @ws_start2 = MIN(sqlserver_start_time) FROM [<<DB>>].[collector].[WaitStats]
    WHERE server_name = @@SERVERNAME AND collection_time = @ws_t2;

    SET @ws_int = CONVERT(nvarchar(20), @ws_t1, 120) + N| -> | + CONVERT(nvarchar(20), @ws_t2, 120);

    IF @ws_start1 IS NOT NULL AND @ws_start1 = @ws_start2
    BEGIN
        CREATE TABLE #ws_deltas (wait_type nvarchar(60), delta_ms bigint);
        INSERT INTO #ws_deltas
        SELECT s2.wait_type, s2.wait_time_ms - s1.wait_time_ms
        FROM [<<DB>>].[collector].[WaitStats] s2
        JOIN [<<DB>>].[collector].[WaitStats] s1
            ON s1.server_name = s2.server_name AND s1.collection_time = @ws_t1 AND s1.wait_type = s2.wait_type
        WHERE s2.server_name = @@SERVERNAME AND s2.collection_time = @ws_t2 AND s2.wait_time_ms > s1.wait_time_ms;

        SELECT @ws_total = SUM(delta_ms) FROM #ws_deltas;

        IF ISNULL(@ws_total, 0) > 0
        BEGIN
            SELECT @ws_pct = CAST(ISNULL(SUM(delta_ms), 0) * 100.0 / @ws_total AS decimal(5,1))
            FROM #ws_deltas WHERE wait_type LIKE |PAGEIOLATCH_%|;
            IF ISNULL(@ws_pct, 0) > 20
                INSERT INTO #findings VALUES (CASE WHEN @ws_pct > 40 THEN |CRITICAL| ELSE |WARNING| END,
                    |wait-stats|, |PAGEIOLATCH_*|, |PAGEIOLATCH_* = | + CAST(@ws_pct AS nvarchar(10)) + |% - | + @ws_int);

            SELECT @ws_pct = CAST(ISNULL(SUM(delta_ms), 0) * 100.0 / @ws_total AS decimal(5,1))
            FROM #ws_deltas WHERE wait_type = |RESOURCE_SEMAPHORE|;
            IF ISNULL(@ws_pct, 0) > 10
                INSERT INTO #findings VALUES (CASE WHEN @ws_pct > 20 THEN |CRITICAL| ELSE |WARNING| END,
                    |wait-stats|, |RESOURCE_SEMAPHORE|, |RESOURCE_SEMAPHORE = | + CAST(@ws_pct AS nvarchar(10)) + |% - | + @ws_int);

            SELECT @ws_pct = CAST(ISNULL(SUM(delta_ms), 0) * 100.0 / @ws_total AS decimal(5,1))
            FROM #ws_deltas WHERE wait_type LIKE |LCK_M_%|;
            IF ISNULL(@ws_pct, 0) > 15
                INSERT INTO #findings VALUES (CASE WHEN @ws_pct > 30 THEN |CRITICAL| ELSE |WARNING| END,
                    |wait-stats|, |LCK_M_*|, |LCK_M_* = | + CAST(@ws_pct AS nvarchar(10)) + |% - | + @ws_int);
        END;
        DROP TABLE #ws_deltas;
    END
    ELSE
        INSERT INTO #findings VALUES (|WARNING|, |wait-stats|, |restart detected|,
            |sqlserver_start_time changed between snapshots - delta skipped|);
END;

-- ── Blocking ──────────────────────────────────────────────────────────────────
DECLARE @blk_count int, @blk_max_ms bigint;
SELECT @blk_count = COUNT(*), @blk_max_ms = MAX(wait_time_ms)
FROM [<<DB>>].[collector].[Blocking]
WHERE server_name = @@SERVERNAME AND collection_time >= DATEADD(HOUR, -2, GETDATE());
IF ISNULL(@blk_count, 0) > 0
    INSERT INTO #findings VALUES (
        CASE WHEN @blk_max_ms > 60000 THEN |CRITICAL| ELSE |WARNING| END,
        |blocking|, |blocking events|,
        CAST(@blk_count AS nvarchar(10)) + | event(s) in last 2h; max wait |
            + CAST(@blk_max_ms / 1000 AS nvarchar(10)) + |s|
    );

-- ── TempDB ───────────────────────────────────────────────────────────────────
DECLARE @latest_tdb datetime2;
SELECT @latest_tdb = MAX(collection_time) FROM [<<DB>>].[collector].[Tempdb] WHERE server_name = @@SERVERNAME;
IF @latest_tdb IS NOT NULL
BEGIN
    INSERT INTO #findings
    SELECT CASE WHEN version_store_mb > 10000 THEN |CRITICAL| ELSE |WARNING| END,
        |tempdb|, |version_store_mb|,
        file_name + |: version_store_mb = | + CAST(version_store_mb AS nvarchar(20)) + | MB|
    FROM [<<DB>>].[collector].[Tempdb]
    WHERE server_name = @@SERVERNAME AND collection_time = @latest_tdb
      AND row_type = |file| AND file_type = |ROWS| AND version_store_mb > 2000;

    INSERT INTO #findings
    SELECT CASE WHEN free_mb < 100 THEN |CRITICAL| ELSE |WARNING| END,
        |tempdb|, |free_mb|,
        file_name + |: free_mb = | + CAST(free_mb AS nvarchar(20)) + | MB|
    FROM [<<DB>>].[collector].[Tempdb]
    WHERE server_name = @@SERVERNAME AND collection_time = @latest_tdb
      AND row_type = |file| AND file_type = |ROWS| AND free_mb < 500;
END;

-- ── Database growth ───────────────────────────────────────────────────────────
-- DatabaseGrowthCurrent is a system-versioned temporal table: it holds exactly one
-- current row per key already, there is no collection_time column to filter on.
IF EXISTS (SELECT 1 FROM [<<DB>>].[collector].[DatabaseGrowthCurrent] WHERE server_name = @@SERVERNAME)
    INSERT INTO #findings
    SELECT CASE growth_status WHEN |AT_LIMIT| THEN |CRITICAL| ELSE |WARNING| END,
        |database-growth|, growth_status,
        |[| + database_name + |] | + logical_name + |: | + CAST(file_size_mb AS nvarchar(20)) + | MB|
        + CASE WHEN growth_limit_mb IS NOT NULL THEN | / | + CAST(growth_limit_mb AS nvarchar(20)) + | MB limit| ELSE || END
    FROM [<<DB>>].[collector].[DatabaseGrowthCurrent]
    WHERE server_name = @@SERVERNAME
      AND growth_status IN (|AT_LIMIT|, |NEAR_LIMIT|);

-- ── VLF count ─────────────────────────────────────────────────────────────────
-- Same story: VlfCountCurrent is system-versioned, already current, no collection_time.
IF EXISTS (SELECT 1 FROM [<<DB>>].[collector].[VlfCountCurrent] WHERE server_name = @@SERVERNAME)
    INSERT INTO #findings
    SELECT CASE WHEN vlf_count > 10000 THEN |CRITICAL| ELSE |WARNING| END,
        |vlf-count|, |vlf_count|,
        |[| + database_name + |] | + CAST(vlf_count AS nvarchar(10)) + | VLFs; reuse_wait: | + log_reuse_wait_desc
    FROM [<<DB>>].[collector].[VlfCountCurrent]
    WHERE server_name = @@SERVERNAME AND vlf_count > 1000;

-- ── Output ────────────────────────────────────────────────────────────────────
IF NOT EXISTS (SELECT 1 FROM #findings)
    PRINT |All checks passed.|;
ELSE
    SELECT severity, collector, check_name, detail
    FROM #findings
    ORDER BY CASE severity WHEN |CRITICAL| THEN 1 ELSE 2 END, collector;

IF EXISTS (SELECT 1 FROM #findings WHERE severity = |CRITICAL|)
    RAISERROR(|Collector alert: CRITICAL findings detected - review job step output.|, 16, 1);'
, N'|', NCHAR(39));

SET @stepCmd = REPLACE(@stepCmd, N'<<DB>>', @TargetDatabase);

-- ═══════════════════════════════════════════════════════════════════════════════
-- DDL output
-- ═══════════════════════════════════════════════════════════════════════════════
SET @ddl =
    N'-- ================================================================' + @crlf +
    N'-- Generated by Generate-CollectorAlertJob.sql'                       + @crlf +
    N'-- Server    : ' + @@SERVERNAME                                       + @crlf +
    N'-- Target DB : ' + @TargetDatabase                                    + @crlf +
    N'-- Generated : ' + CONVERT(nvarchar(20), GETDATE(), 120)              + @crlf +
    N'-- ================================================================' + @crlf + @crlf;

-- ── Agent category ────────────────────────────────────────────────────────────
SET @ddl +=
    N'USE msdb;' + @crlf +
    N'GO' + @crlf + @crlf +
    N'IF NOT EXISTS (SELECT 1 FROM msdb.dbo.syscategories WHERE name = N' + @q + @CategoryName + @q + N' AND category_class = 1)' + @crlf +
    N'    EXEC msdb.dbo.sp_add_category'                                                                                             + @crlf +
    N'        @class = N' + @q + N'JOB' + @q + N', @type = N' + @q + N'LOCAL' + @q + N', @name = N' + @q + @CategoryName + @q + N';' + @crlf +
    N'GO' + @crlf + @crlf;

-- ── Job + step + schedule ─────────────────────────────────────────────────────
SET @ddl +=
    N'-- Job: ' + @jobName + @crlf +
    N'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N' + @q + @jobName + @q + N')' + @crlf +
    N'    EXEC msdb.dbo.sp_delete_job @job_name = N' + @q + @jobName + @q + N', @delete_unused_schedule = 1;' + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_job'                                                    + @crlf +
    N'    @job_name         = N' + @q + @jobName + @q + N','                       + @crlf +
    N'    @enabled          = 1,'                                                   + @crlf +
    N'    @owner_login_name = N' + @q + @JobOwner + @q + N','                      + @crlf +
    N'    @category_name    = N' + @q + @CategoryName + @q + N';'                  + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_jobstep'                                                + @crlf +
    N'    @job_name          = N' + @q + @jobName + @q + N','                      + @crlf +
    N'    @step_id           = 1,'                                                  + @crlf +
    N'    @step_name         = N' + @q + N'Run collector threshold checks' + @q + N',' + @crlf +
    N'    @subsystem         = N' + @q + N'TSQL' + @q + N','                       + @crlf +
    N'    @database_name     = N' + @q + N'master' + @q + N','                     + @crlf +
    N'    @command           = N' + @q + REPLACE(@stepCmd, @q, @q + @q) + @q + N',' + @crlf +
    N'    @retry_attempts    = 0,'                                                  + @crlf +
    N'    @on_success_action = 1,'                                                  + @crlf +
    N'    @on_fail_action    = 2;'                                                  + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_schedule'                                               + @crlf +
    N'    @schedule_name        = N' + @q + @jobName + N' Every ' + CAST(@IntervalMinutes AS nvarchar(5)) + N'min' + @q + N',' + @crlf +
    N'    @freq_type            = 4,'                                               + @crlf +   -- daily recurring
    N'    @freq_interval        = 1,'                                               + @crlf +
    N'    @freq_subday_type     = 4,'                                               + @crlf +   -- minutes
    N'    @freq_subday_interval = ' + CAST(@IntervalMinutes AS nvarchar(5)) + N';' + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_attach_schedule'                                            + @crlf +
    N'    @job_name      = N' + @q + @jobName + @q + N','                          + @crlf +
    N'    @schedule_name = N' + @q + @jobName + N' Every ' + CAST(@IntervalMinutes AS nvarchar(5)) + N'min' + @q + N';' + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_jobserver @job_name = N' + @q + @jobName + @q + N';'   + @crlf +
    N'GO' + @crlf;

SELECT @ddl AS ddl;

Any login can run the generator, it only builds a string. Run the DDL it returns as sysadmin.


How To Run From The Repo

git clone https://github.com/peterwhyte-lgtm/dba-tools
cd dba-tools
.\Initialize-Environment.ps1

.\run.ps1 Generate-CollectorAlertJob
# Review the generated DDL, then run it on the target instance
# (install the individual collector jobs first, this job reads their tables)

This script lives in the repo at:


Example Output

The generator returns one ddl cell: a job category, the job with one T-SQL step, and a 30-minute schedule. This is what that step printed on the lab against the real collector tables.

DBA - Collector Alert step on the lab instanceexample output, not something to copy
severity collector check_name detail -------- --------------- ---------- -------------------------------------------------------------------- CRITICAL database-growth AT_LIMIT [Lab1003_fs_capped] Lab1003_fs_capped: 64.00 MB / 64.00 MB limit CRITICAL database-growth AT_LIMIT [Lab1003_fs_capped] Lab1003_fs_capped_log: 64.00 MB / 64.00 MB limit CRITICAL database-growth AT_LIMIT [Lab1001_logfull] Lab1001_logfull_log: 48.00 MB / 48.00 MB limit CRITICAL tempdb free_mb tempdev: free_mb = 68.69 MB CRITICAL tempdb free_mb temp2: free_mb = 71.38 MB CRITICAL tempdb free_mb temp3: free_mb = 71.69 MB CRITICAL tempdb free_mb temp4: free_mb = 71.00 MB CRITICAL tempdb free_mb temp5: free_mb = 71.13 MB CRITICAL tempdb free_mb temp6: free_mb = 71.50 MB CRITICAL tempdb free_mb temp7: free_mb = 71.13 MB CRITICAL tempdb free_mb temp8: free_mb = 71.38 MB WARNING database-growth NEAR_LIMIT [Lab1001_msdb] Lab1001_msdb_log: 31.88 MB / 32.00 MB limit WARNING database-growth NEAR_LIMIT [pass_agt] pass_agt: 102.00 MB / 1024.00 MB limit WARNING database-growth NEAR_LIMIT [pass_agt] pass_agt_log: 8.00 MB / 64.00 MB limit WARNING database-growth NEAR_LIMIT [pass_gr] pass_gr: 112.00 MB / 128.00 MB limit WARNING database-growth NEAR_LIMIT [pass_gr] pass_gr_log: 56.00 MB / 64.00 MB limit WARNING database-growth NEAR_LIMIT [pass_gr_fc] pass_gr_fc: 502.00 MB / 1024.00 MB limit

The step then raised Msg 50000, Level 16, Collector alert: CRITICAL findings detected – review job step output., which is what fails the job. Read it with the panel below: the 8 TempDB rows are one cause, and every database-growth row named a database that had already been dropped.


Understanding the Results

severity
CRITICAL makes the step raise Msg 50000 at severity 16, so Agent marks the job failed. WARNING never fails it: those rows exist only in the step output inside job history.Act when the job shows succeeded: open its history anyway, a green run can still carry WARNING rows.
wait-stats
Compares the last 2 snapshots. Share of wait time between them: PAGEIOLATCH_* over 20% WARNING, over 40% CRITICAL; RESOURCE_SEMAPHORE 10% and 20%; LCK_M_* 15% and 30%. A restart between the snapshots gives a restart detected WARNING instead.Act when one wait dominates: look it up in the wait statistics library.
blocking
Any blocking event collected in the last 2 hours is a WARNING; a longest wait over 60 seconds makes it CRITICAL.Act when it repeats run after run: find the head blocker with Collect Blocking and Deadlock Events.
tempdb
One row per data file from the latest snapshot: free space under 500 MB WARNING, under 100 MB CRITICAL; version store over 2,000 MB WARNING, over 10,000 MB CRITICAL.Act when every file reports at once: that is one cause (small or full files), not 8 problems.
database-growth
AT_LIMIT (file at its max_size) is CRITICAL; NEAR_LIMIT (under 1,024 MB left before max_size) is WARNING. Both statuses are set by the database growth collector.Act when a row names a database: check it still exists first. The collector never removes rows for dropped databases, and on the lab all 9 growth findings named one.
vlf-count
Over 1,000 VLFs in a log is a WARNING, over 10,000 CRITICAL, shown with the log’s log_reuse_wait_desc.Act when the count is high: see Get VLF Count before shrinking anything.
How old the data is
Nothing checks it. The step reads the latest snapshot however old it is, and the blocking check only looks back 2 hours. On the lab the collector jobs had stopped 36 hours earlier and the step still reported their TempDB numbers as CRITICAL.Act when this job goes quiet or repeats itself: check the collector jobs are still succeeding with Get SQL Agent Job Failure Summary.

Who Gets Told

Nobody, until you wire it. The generated job sets no notification, and on the lab it came out with notify_level_email = 0, so a CRITICAL fails the job and the only trace is job history. Once the job exists, point it at an operator with sp_update_job:

EXEC msdb.dbo.sp_update_job
    @job_name                   = N'DBA - Collector Alert',
    @notify_level_email         = 2,   -- on failure
    @notify_email_operator_name = N'DBA Team';

Then check the 3 things that make that e-mail arrive:

  • The operator exists. List operators and their addresses with Get Agent Alerts and Operators.
  • Database Mail works, and Agent uses it. Agent needs a mail profile in its Alert System properties; without one the notification goes nowhere. Check with Get Database Mail and xp_cmdshell Configuration.
  • You re-applied it after regenerating. Running the generated DDL again drops and recreates the job, and on the lab the operator setting went back to 0. Run the sp_update_job again every time.

Best Practices

  • Install all 5 collectors it reads first (wait stats, blocking, TempDB, database growth, VLF count). A missing table fails the whole step, every 30 minutes, for a reason that is not a finding.
  • Treat any CRITICAL finding from this job as corroborated evidence, not a first alert, cross-check it against the specific collector’s own post (Capacity/TempDB, Performance Baselines, or Health/Configuration) for full context before acting.
  • Review and adjust the fixed thresholds for your own environment, 100MB free per TempDB file is a reasonable generic default, not a number tuned to any specific workload.
  • Wire an operator (Who Gets Told) and re-apply it after every regenerate; the whole point is not having to remember to look.

MS Docs covers sp_add_job, sp_update_job, sp_add_category and sp_delete_job in full.

Related Scripts

You may also find these scripts useful:


Frequently Asked Questions

Do I need every collector installed before this alert job will work?

Yes. The step reads all 5 collector tables in one batch, and a missing one stops it. With only the wait stats table in place, the lab run stopped at Msg 208, Level 16, Invalid object name ‘…collector.Blocking’, and no TempDB, growth or VLF check ran. Install all 5, or delete the section for the one you do not run before you generate.

Why does the alert job only check DatabaseGrowthCurrent and VlfCountCurrent, not their History tables?

The Current tables already hold the latest state per key, exactly what a threshold check needs. The History tables exist for trend analysis, not real-time alerting, checking them here would mean re-deriving “what’s current” from history, work the Current table already does for you.

Why is it alerting on a database that no longer exists?

The database growth collector keeps one row per file and never deletes rows for dropped databases. On the lab 77 of the 118 rows in DatabaseGrowthCurrent belonged to dropped databases, and every database-growth finding named one. Clear them with DELETE g FROM DBAMonitor.collector.DatabaseGrowthCurrent AS g WHERE g.server_name = @@SERVERNAME AND DB_ID(g.database_name) IS NULL;, the history table keeps the old rows.

What permission does it need?

To generate: none beyond connecting. A login with only CONNECT SQL got the same DDL as sysadmin.

To run the output: sysadmin. As a SQLAgentOperatorRole member it failed at sp_add_category (Msg 229), then at sp_add_job with Msg 14515, because the job owner is sa and, per MS Docs, only sysadmin can set another owner. It still left an unattached schedule behind.

What happens if I run the generated script twice?

It replaces the job rather than duplicating it: on the lab the second run left 1 job and 1 schedule. The job comes back enabled even if you had disabled it, under a new job_id, and sp_delete_job removes its history and the operator setting with it.

One case fails: if an old schedule called DBA - Collector Alert Every 30min is left over, sp_attach_schedule stops with Msg 14371, There are two or more schedules named …, and the job is created with no schedule, so it never runs. Delete the stray schedule and run it again.


Summary

One job that reads the 5 collector tables every 30 minutes and fails on any CRITICAL finding. Before you trust it, wire an operator and Database Mail, install all 5 collectors, and clear dropped databases out of DatabaseGrowthCurrent, or it will page you about databases that are gone. The raw numbers behind each finding are in Collect Capacity and Temp DB Baselines and its sibling collectors.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *