Jobs & Collectors¶

Every SQL instance under SQLMonitor — including the inventory server — runs a set of SQL Agent jobs in the (dba) SQLMonitor category. They are the entire data-collection plane: they poll the OS, DMVs, WMI, XEvents and Perfmon, and persist everything into the DBA database for Grafana and the alert engine to consume.

What you get out of the box¶

• 46 named jobs installed in the (dba) SQLMonitor category
• Grouped by purpose below (Collection, Aggregation, Ops/Health, Maintenance, Diagnostics)
• Every job is defined by a single DDLs/SCH-Job-[*].sql script — the installer simply runs them
• Schedules range from every 10 seconds (alert polling) to weekly (Blitz reports)
• Most jobs run as CmdExec under the sqlmonitor proxy — they shell out to sqlcmd so long-running T-SQL cannot pin the job worker thread

The per-instance vs. inventory split¶

See Architecture → Data Flow for the pull path.

Anatomy of a collector job¶

Every (dba) Collect-* and (dba) Get-AllServer* job follows the same shape (by design — makes Install-SQLMonitor.ps1 idempotent):

```sql -- DDLs/SCH-Job-[(dba) Collect-WaitStats].sql (excerpt) EXEC msdb.dbo.sp_add_job @job_name = N'(dba) Collect-WaitStats', @category_name = N'(dba) SQLMonitor', @owner_login_name = N'sa', @description = N'Collect cumulative wait stats on server into dbo.wait_stats.';

EXEC msdb.dbo.sp_add_jobstep @step_name = N'Collect Data', @subsystem = N'CmdExec', @proxy_name = N'sqlmonitor', -- standard SQLMonitor proxy @command = N'sqlcmd -S $(ESCAPE_SQUOTE(SRVR)) -d DBA -E -Q "EXEC dbo.usp_collect_wait_stats"';

EXEC msdb.dbo.sp_add_jobschedule @freq_type = 4, -- daily @freq_subday_type = 4, @freq_subday_interval = 10; -- every 10 minutes ```

That means:

1. The business logic lives in the stored procedure (dbo.usp_collect_wait_stats), not in the job. Jobs are deliberately thin wrappers — you can re-run the underlying proc from SSMS whenever you like.
2. The SQL Agent proxy sqlmonitor is used instead of the SQL Agent service account. The proxy maps to a Windows credential with just enough rights to read Perfmon/Registry/Services. See Deployment → Prerequisites.
3. The schedule is encoded in freq_type/freq_subday_type/freq_subday_interval. The tables below decode those into human form.

Monitoring the collectors themselves¶

Jobs that are themselves not collecting on time will cause dashboards to go stale. SQLMonitor ships three self-monitors for that:

Scheduling conventions¶

The Install-SQLMonitor.ps1 -JobCollectionFrequency parameter (see Parameters) can uniformly increase every 1-minute job to 2, 5, 10 or 30 minutes. Use this on larger fleets where the inventory server can't keep up with 1-minute pulls. Set it in your Wrapper-Install-SQLMonitor.ps1 — not by editing individual SCH-Job-*.sql files.

Full job catalog¶

Below is the authoritative list of all 46 jobs that Install-SQLMonitor.ps1 creates, grouped by purpose. Links point at the exact SCH-Job-*.sql script the installer uses.

Collection¶

Aggregation¶

Ops/Health¶

Maintenance¶

Diagnostics¶

Total: 46 jobs in the (dba) SQLMonitor category.

Triggering jobs outside their schedule¶

Every collector can be run manually:

```sql -- On the target instance EXEC msdb.dbo.sp_start_job @job_name = N'(dba) Collect-WaitStats';

-- Or run the proc directly (same effect, faster iteration) EXEC DBA.dbo.usp_collect_wait_stats; ```

For the inventory side, use @sql_instance to scope:

sql EXEC DBA.dbo.usp_GetAllServerInfo @collection_name = N'volatile_info';

Disabling collectors¶

If a collector is noisy for your environment, disable it rather than dropping it — that way Install-SQLMonitor.ps1 will not recreate it on the next upgrade (the installer respects existing job enabled/disabled state):

sql EXEC msdb.dbo.sp_update_job @job_name = N'(dba) Collect-XEvents', @enabled = 0;

Related reading¶

• Deployment → Install Steps — maps each job-creation step name to the SQL file.
• Architecture → Components — where these jobs fit in the overall flow.
• Alerting — how dbo.alert_history is populated and consumed.