SQL Agent Jobs & Schedules

Status: Reviewed (deep-dive v1)
Stack: PowerShell census → SQL history warehouse/procs → Blazor Server UI
Depends on: SQL-01 Inventory (instance list); watches jobs from SQL-02/03 (backups, index maint)
Goal: Know which jobs matter, when they fail or run long, and that nothing critical is missing or overlapping.

Why this fourth

Backups and index work only help if their Agent jobs actually run. Job failure and silent disable are common outage causes. Monitor the babysitters.

What “good” looks like

Critical jobs tagged (backup full/diff/log, index/stats, CHECKDB, ETL, AG/DR helpers)
Failed / canceled / long-running jobs surface in Blazor quickly
Duration baselines exist (p50/p95 or simple avg + threshold)
Disabled critical jobs flagged as Red
Overlap / runaway detection for heavy maintenance
Operators + mail profile health visible (tied to inventory)

Job criticality (starting tags — confirm with Rick)

Tag	Examples	Alert bias
Critical	Full/log backup, AG backups, CHECKDB (prod), payroll ETL	Page / immediate
High	Index maint execute, stats, diff backup	Same-day
Standard	Housekeeping, cycle error log	Daily digest
Ignore	Ad-hoc, disabled leftovers (documented)	Hide from main board

Maintain a JobCatalog override table so rename/category quirks don’t drop critical coverage.

Checklist — collect (PowerShell + T-SQL)

Scope = inventoried instances where Agent should run.

[ ] Agent service state (PS: service SQLSERVERAGENT / named SQLAgent$*)
[ ] Job list: name, enabled, category, owner, date created/modified, description
[ ] Schedules: frequency, active start/end, next run (from msdb schedule tables)
[ ] Recent history: msdb.dbo.sysjobhistory (outcome, run_duration, message) — last N days
[ ] Currently running: sysjobactivity / sp_help_jobactivity
[ ] Operators / notifications on critical jobs (notify level email)
[ ] Emit payload with instance key + CollectedAt

Read-only collect. Don’t start/stop jobs from the collector.

Checklist — store (SQL + stored procedures)

Table	Purpose
`dbo.SqlAgentJob`	Job identity + enabled + category + criticality tag
`dbo.SqlAgentSchedule`	Schedule facts per job
`dbo.SqlAgentJobHistory`	Trimmed history (retain 30–90 days)
`dbo.SqlAgentJobBaseline`	Duration baseline per job
`dbo.SqlAgentJobStatus`	Latest outcome, last success, running flag, overdue schedule

Procs:

[ ] usp_SqlAgentJob_Upsert — merge job + schedules
[ ] usp_SqlAgentHistory_Apply — load history; update status + baselines
[ ] usp_SqlAgent_GetFailBoard — Blazor failed/long/disabled-critical
[ ] usp_SqlAgent_GetJobDetail — history sparkline + last messages
[ ] usp_SqlAgent_GetMissingCritical — expected tags with no matching job

Long-running rule (start): running longer than max(2× baseline, baseline + 30 min) → Yellow/Red.

Overdue rule: enabled critical job with schedule and LastSuccess older than schedule interval × 2.

Checklist — present (Blazor Server)

[ ] Fail board: failed (24h), disabled critical, overdue, long-running
[ ] Filters: instance, criticality, category contains “Backup” / “Database Maintenance”
[ ] Detail blade: last 20 history rows, schedules, operators, baseline duration
[ ] Home badge: count of critical failures
[ ] Actions v1: link runbook + “ack” note only (no start/stop from UI yet)

Operator runbook (short)

Open Fail board → Critical first
Read history message (permission, disk, timeout, deadlock victim, step N)
Confirm Agent service running (inventory / PS)
If backup job failed → jump to SQL-02 coverage for that DB
If index job runaway → check SQL-03 window/budget; blocking; log disk
If disabled unexpectedly → change control? re-enable with approval
After fix: confirm next success or run manually under change control; wait for collect to clear Red

Common failure patterns

Symptom	Likely cause
All jobs failing	Agent stopped; msdb issue; credentials
One step fails	Proxy/subsystem; path; linked server
Succeeds but overdue later	Schedule disabled; daylight change; AG failover job ownership
Duration creep	Data growth; competing maint; blocking
No history	Job never ran; Agent history retention too aggressive; wrong instance

Overlap & runaway controls

[ ] Don’t schedule full backup + index rebuild + CHECKDB on same tight window without stagger
[ ] Cap index execute job runtime (see SQL-03)
[ ] Alert when two Critical heavy jobs run concurrently on one host (PS or SQL activity)
[ ] Document exclusive windows per instance in IndexMaintPolicy / runbook

Permissions notes

Collector: read msdb job tables; PS service query
Writer: upsert only warehouse tables
Blazor: read procs
Manual job start: DBA role / change control — not app pool

Done definition for this topic

[ ] Job census for inventoried instances
[ ] Fail board shows real failed/disabled-critical cases
[ ] At least one backup or maint job tagged Critical and monitored
[ ] This note linked from the Knowledge Base list item

Next after this

Security & access hygiene — who can change these jobs and what logins exist.

SQL Dude — SQL-04 SQL Agent Jobs & Schedules v1