Part of the DBA-Tools Project.
The Job That Reads Every Other Collector and Actually Tells You Something’s Wrong
Every collector in this pillar writes evidence somewhere, blocking chains, deadlocks, wait stats, VLF counts, database growth, TempDB pressure. None of them, on their own, tells anyone anything. Generate-CollectorAlertJob.sql is the cross-cutting piece: one recurring job that reads across all the collector tables, checks each against a threshold, and surfaces CRITICAL and WARNING findings in one place. Building it against real collector data, on this same lab instance, surfaced two genuine bugs, and after fixing both, a genuinely successful run that surfaced a real CRITICAL finding.
Two Real Bugs, Found Running It Against Real Data
1. Wrong table names. The alert job’s threshold checks referenced [collector].[DatabaseGrowth] and [collector].[VlfCount], tables that don’t exist. The actual tables, created by their own collector generators, are DatabaseGrowthCurrent and VlfCountCurrent. Running the alert job against real collector data failed immediately:
Msg 208, Level 16, State 1
Invalid object name 'DBAMonitor.collector.DatabaseGrowth'.
Fixed by correcting all four references to the actual table names.
2. A staleness filter on columns that don’t exist. The original logic tried to check MAX(collection_time) on DatabaseGrowthCurrent and VlfCountCurrent before alerting, treating them like the simple timestamped collector tables elsewhere in this pillar. But both are system-versioned temporal tables (see Collect Capacity and Temp DB Baselines), they hold exactly one current row per key by design, there is no collection_time column to check:
Msg 207, Level 16, State 1
Invalid column name 'collection_time'.
Fixed by removing the staleness check entirely for these two tables and querying the Current tables directly, they’re always current by construction, a staleness filter on top would have been redundant even if the column had existed.
Generate-CollectorAlertJob.sql
/*
Script Name : Generate-CollectorAlertJob
Category : collectors
Purpose : Generates DDL to create the DBA - Collector Alert Check SQL Agent job.
Reads across every collector table (Blocking, Deadlocks, WaitStats,
StorageIO, VlfCountCurrent, DatabaseGrowthCurrent, TempDB) and raises
CRITICAL / WARNING findings against fixed thresholds in one place.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-generate-collector-alerts/)
Requires : sysadmin (to run generated DDL); VIEW SERVER STATE at job runtime
Notes : Default interval: every 15 minutes.
Depends on the individual collector jobs already being installed and running.
*/
(The full script is in the repo, link below.)
Real Output — After Both Fixes, Against This Instance’s Real Collector Data
severity category finding
CRITICAL TempDB File 'tempdev' has only 5.13 MB free (limit: 100 MB)
CRITICAL TempDB File 'temp2' has only 7.81 MB free (limit: 100 MB)
CRITICAL TempDB File 'temp3' has only 7.88 MB free (limit: 100 MB)
CRITICAL TempDB File 'temp4' has only 7.88 MB free (limit: 100 MB)
CRITICAL TempDB File 'temp5' has only 7.69 MB free (limit: 100 MB)
8 CRITICAL TempDB findings in total, every one of them genuine, this lab instance’s TempDB really is under 8MB free per file, the same underlying pressure Collect Capacity and Temp DB Baselines surfaced independently from the raw collector data. Two different scripts, reading the same real condition, agreeing with each other, not a coincidence, evidence the alert logic is actually threshold-checking real numbers rather than returning a canned result.
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:
Understanding the Results
- An
Invalid object nameerror naming a collector table almost always means that collector’s generator hasn’t been run yet on this instance, or was renamed since. Install the individual collector jobs first, this alert job only reads what they’ve already created. - A system-versioned “Current” table has no
collection_timecolumn by design, and doesn’t need one. If you extend this alert job to cover another temporal collector table, query it directly rather than adding a staleness filter that assumes a timestamp column exists. - Multiple CRITICAL findings on the same object family (all five TempDB files here) usually point to one root cause, in this case genuinely low configured TempDB file size on this lab instance, not five independent problems.
Best Practices
- Install this job only after the individual collectors it depends on are already running, an alert check against empty or missing tables produces false silence, not a useful “all clear.”
- 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.
- Route this job’s findings into whatever alerting channel you already use (email, Teams, PagerDuty) rather than only checking the table manually, the whole point is not having to remember to look.
Related Scripts
You may also find these scripts useful:
- Collectors and Baseline Infrastructure (hub)
- Collect Capacity and Temp DB Baselines
- Collect Blocking and Deadlock Events
- Collect Performance Baselines
Frequently Asked Questions
Do I need every collector installed before this alert job will work?
No, it checks each collector table independently. A missing table for a collector you haven’t installed simply means that category never raises findings, it won’t break the checks for collectors you do have running. Installing all of them gets you full coverage.
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.
Summary
One job that reads across every collector this pillar builds and turns raw collected data into CRITICAL and WARNING findings in a single place. Building and testing it against this instance’s real collector data surfaced two genuine bugs, wrong table names and a staleness check that assumed a column existing on ordinary tables also existed on system-versioned ones. After both fixes, a real end-to-end run surfaced 8 genuine CRITICAL TempDB findings, corroborating the same low-free-space condition the TempDB collector itself found independently.
Leave a Reply