SQL Agent system stored procedures

Status: Reviewed
Stack: PowerShell → SQL / stored procs → Blazor
Depends on: SQL-CLI-09 (overview), SQL-CLI-01 (calling), SQL-CLI-07 (pipeline shapes)
Goal: Know when to use msdb Agent control-plane procs (sp_help_job*, sp_start_job / sp_stop_job, sp_update_job, …) vs querying msdb tables for Blazor inventory — and the permission / multi-set / platform gotchas.

Sources: Microsoft Learn (sql-server-ver17 / 2026). Expands SQL-CLI-09 §3.4 only — do not treat this as a rewrite of the overview. Calling: SQL-CLI-01. Staging → Blazor: SQL-CLI-07.

1. Scope map

Control plane (ops): Agent procs in msdb — start/stop, enable/disable, interactive help.
Read path for Blazor: always msdb tables (single-select joins) — never scrape multi-result sp_help_job into a grid.

Platform: SQL Server Agent lives on SQL Server and Azure SQL Managed Instance (MI has feature gaps). Azure SQL Database has no Agent — use Elastic Jobs (jobs.sp_* in the job DB) or external schedulers. Same proc names in Elastic Jobs are a different API.

2. Decision: Agent procs vs msdb tables / DMVs

Multi-result-set problem (same rule as 09b / 09c)

Rule for this stack: Collect with a single-select against msdb tables into dbo.*Staging (CLI-07). Use Agent help procs for interactive triage; use start/stop/update for control plane only.

3. Curated high-value procs

All live in msdb. Call as EXEC msdb.dbo.sp_… when the session DB isn’t msdb. Columns per proc: purpose · args · quirks · perms · prefer-instead.

3.1 Inventory / help

sp_help_job

EXEC msdb.dbo.sp_help_job;                                      -- interactive list
EXEC msdb.dbo.sp_help_job @job_name = N'NightlyBackups', @job_aspect = N'ALL';

sp_help_jobactivity

EXEC msdb.dbo.sp_help_jobactivity;  -- all jobs caller can see (latest session)

sp_help_jobhistory

EXEC msdb.dbo.sp_help_jobhistory @job_name = N'NightlyBackups', @mode = N'FULL';

sp_help_jobstep / sp_help_schedule

UserRole: owned jobs/schedules only.

3.2 Control plane

sp_start_job / sp_stop_job

EXEC msdb.dbo.sp_start_job @job_name = N'Weekly Sales Data Backup';
EXEC msdb.dbo.sp_stop_job  @job_name = N'Weekly Sales Data Backup';

sp_update_job (ops focus)

EXEC msdb.dbo.sp_update_job @job_name = N'NightlyBackups', @enabled = 0;

sp_add_job* family (thin)

3.3 History purge

sp_purge_jobhistory

EXEC msdb.dbo.sp_purge_jobhistory
  @job_name = N'NightlyBackups',
  @oldest_date = '2025-01-01';

4. Permissions (SQL Agent fixed database roles)

Roles live in msdb, concentric (more privilege ⊆ less). Users need one of these or sysadmin to use Agent; otherwise the Agent node is invisible in SSMS.

Proxy inheritance: higher roles inherit proxies granted to lower roles — grant proxies carefully.

Collector login for Blazor: prefer SQLAgentReaderRole (read-wide) without Operator purge/start power, unless the app must trigger jobs (then Operator or a dedicated control path).

5. Gotchas

6. Blazor inventory via msdb tables (not help procs)

Single-select shapes → stage → Blazor (CLI-07). Prefer documented DATETIMEFROMPARTS over undocumented helpers.

6.1 Job list

SET NOCOUNT ON;
SELECT
  j.job_id,
  j.name AS job_name,
  j.enabled,
  j.category_id,
  SUSER_SNAME(j.owner_sid) AS owner_name,
  j.date_created,
  j.date_modified,
  j.description,
  SYSUTCDATETIME() AS collected_utc
FROM msdb.dbo.sysjobs AS j
ORDER BY j.name;

6.2 Current activity (latest Agent session)

SET NOCOUNT ON;
SELECT
  j.name AS job_name,
  a.start_execution_date,
  a.stop_execution_date,
  a.next_scheduled_run_date,
  a.last_executed_step_id,
  CASE
    WHEN a.start_execution_date IS NOT NULL AND a.stop_execution_date IS NULL THEN N'Running'
    ELSE N'Not running'
  END AS run_status_label
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobactivity AS a ON a.job_id = j.job_id
WHERE a.session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity);

6.3 Recent history (int → datetime)

SET NOCOUNT ON;
SELECT TOP (500)
  j.name AS job_name,
  h.step_id,
  h.step_name,
  h.run_status,
  DATETIMEFROMPARTS(
    h.run_date / 10000, h.run_date % 10000 / 100, h.run_date % 100,
    h.run_time / 10000, h.run_time % 10000 / 100, h.run_time % 100, 0
  ) AS run_start,
  (h.run_duration / 10000) * 3600
    + ((h.run_duration % 10000) / 100) * 60
    + (h.run_duration % 100) AS run_duration_seconds,
  h.message
FROM msdb.dbo.sysjobhistory AS h
INNER JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
ORDER BY h.instance_id DESC;

6.4 Steps + schedules (definitions)

-- Steps
SELECT j.name AS job_name, s.step_id, s.step_name, s.subsystem,
       s.database_name, s.command
FROM msdb.dbo.sysjobsteps AS s
JOIN msdb.dbo.sysjobs AS j ON j.job_id = s.job_id;

-- Schedules attached to jobs
SELECT j.name AS job_name, sch.name AS schedule_name, sch.enabled,
       sch.freq_type, js.next_run_date, js.next_run_time
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobschedules AS js ON js.job_id = j.job_id
JOIN msdb.dbo.sysschedules AS sch ON sch.schedule_id = js.schedule_id;

7. Copy-paste recipes (sqlcmd + Invoke-Sqlcmd)

Assume Windows auth (-E). Swap to CLI-06 for Entra/SQL auth. Always -b / check exit codes (CLI-01, CLI-07).

R1 — List jobs (interactive help)

sqlcmd -S "$SQL_SERVER" -E -d msdb -b -Q "EXEC dbo.sp_help_job;"

R2 — Job activity snapshot

sqlcmd -S "$SQL_SERVER" -E -d msdb -b -Q "EXEC dbo.sp_help_jobactivity;"

R3 — Start a job

sqlcmd -S "$SQL_SERVER" -E -d msdb -b -Q \
  "EXEC dbo.sp_start_job @job_name = N'NightlyBackups';"

R4 — Stop a job

sqlcmd -S "$SQL_SERVER" -E -d msdb -b -Q \
  "EXEC dbo.sp_stop_job @job_name = N'NightlyBackups';"

R5 — Disable / enable via update

sqlcmd -S "$SQL_SERVER" -E -d msdb -b -Q \
  "EXEC dbo.sp_update_job @job_name = N'NightlyBackups', @enabled = 0;"

R6 — Full history for one job (interactive)

sqlcmd -S "$SQL_SERVER" -E -d msdb -b -Q \
  "EXEC dbo.sp_help_jobhistory @job_name = N'NightlyBackups', @mode = N'FULL';"

R7 — Job inventory CSV (tables → Blazor staging)

$q = @"
SET NOCOUNT ON;
SELECT j.name AS JobName, j.enabled AS IsEnabled,
       SUSER_SNAME(j.owner_sid) AS OwnerName,
       j.date_modified AS DateModified, SYSUTCDATETIME() AS CollectedUtc
FROM msdb.dbo.sysjobs AS j
ORDER BY j.name;
"@
Invoke-Sqlcmd -ServerInstance $env:SQL_SERVER -Database msdb -TrustServerCertificate -Query $q |
  Export-Csv ".\staging\jobs_$($env:SQL_SERVER -replace '\\','_').csv" -NoTypeInformation

R8 — Running jobs (activity table)

$q = @"
SET NOCOUNT ON;
SELECT j.name AS JobName, a.start_execution_date AS StartExecutionDate,
       a.last_executed_step_id AS LastStepId
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobactivity AS a ON a.job_id = j.job_id
WHERE a.session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)
  AND a.start_execution_date IS NOT NULL
  AND a.stop_execution_date IS NULL;
"@
Invoke-Sqlcmd -ServerInstance $env:SQL_SERVER -Database msdb -TrustServerCertificate -Query $q |
  ConvertTo-Json -Depth 3 |
  Set-Content ".\running-jobs-$($env:SQL_SERVER -replace '\\','_').json" -Encoding UTF8

R9 — Failed history last 7 days → JSON

$q = @"
SET NOCOUNT ON;
SELECT j.name AS JobName, h.step_name AS StepName, h.run_status AS RunStatus,
       DATETIMEFROMPARTS(
         h.run_date/10000, h.run_date%10000/100, h.run_date%100,
         h.run_time/10000, h.run_time%10000/100, h.run_time%100, 0) AS RunStart,
       h.message AS Message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
WHERE h.run_status = 0
  AND DATETIMEFROMPARTS(
        h.run_date/10000, h.run_date%10000/100, h.run_date%100,
        h.run_time/10000, h.run_time%10000/100, h.run_time%100, 0)
      >= DATEADD(DAY, -7, SYSUTCDATETIME());
"@
Invoke-Sqlcmd -ServerInstance $env:SQL_SERVER -Database msdb -TrustServerCertificate -Query $q |
  ConvertTo-Json -Depth 3 |
  Set-Content ".\job-failures-7d.json" -Encoding UTF8

R10 — Purge history older than retention (named job)

sqlcmd -S "$SQL_SERVER" -E -d msdb -b -Q \
  "EXEC dbo.sp_purge_jobhistory @job_name = N'NightlyBackups', @oldest_date = '2025-01-01';"

8. Pointers to 09 / 09a–c / 09e

Related stack: SQL-CLI-01 (calling / -b / -d), SQL-CLI-07 (Collect→Stage→Blazor).

Learn anchors (ver17): SQL Server Agent Tables, SQL Server Agent Fixed Database Roles, sp_help_job, sp_help_jobactivity, sp_help_jobhistory, sp_help_jobstep, sp_help_schedule, sp_start_job, sp_stop_job, sp_update_job, sp_purge_jobhistory, dbo.sysjobs, dbo.sysjobsteps, dbo.sysjobhistory, dbo.sysjobactivity, dbo.sysschedules, dbo.sysjobschedules, Job automation with SQL Agent Jobs (Azure SQL Managed Instance), Elastic Jobs overview (Azure SQL Database).