Keeping SQL Agent job history for years: an incremental load into a small data mart

#Why

msdb history is short-lived and per server. A central history store gives you:

• Duration trends over months ("this job is 3% slower every month").
• Failure rates per job, team and server.
• A source for dashboards (see the Power BI post).

#Schema

CREATE SCHEMA agent;
GO
CREATE TABLE agent.DimServer (server_key int IDENTITY PRIMARY KEY, server_name sysname UNIQUE);
CREATE TABLE agent.DimJob (
  job_key int IDENTITY PRIMARY KEY, server_key int NOT NULL, job_id uniqueidentifier NOT NULL,
  job_name sysname, category sysname, owner sysname, enabled bit,
  CONSTRAINT UQ_job UNIQUE (server_key, job_id));
CREATE TABLE agent.FactJobRun (
  server_key int NOT NULL, job_key int NOT NULL, instance_id int NOT NULL,
  step_id int NOT NULL, step_name sysname, run_status tinyint,
  run_start datetime2(0), duration_sec int, retries int, message nvarchar(4000),
  CONSTRAINT PK_FactJobRun PRIMARY KEY (server_key, instance_id));
CREATE TABLE agent.LoadWatermark (server_key int PRIMARY KEY, last_instance_id int NOT NULL);

instance_id grows within a server, so it works as the watermark. Watch for one edge case: if msdb is rebuilt, ids restart. Reset the watermark when MAX(instance_id) on the source is lower than the stored one.

#Load procedure (run on each source server, writing to the central server)

Simplest version: a job on each server pushes new rows through a linked server, or a central PowerShell collector pulls them. The pull version:

Import-Module dbatools
$central = 'SQLDBA01'; $db = 'OpsMart'
$servers = Invoke-DbaQuery -SqlInstance $central -Database $db -Query "SELECT server_name FROM agent.DimServer" | Select-Object -Expand server_name

foreach ($s in $servers) {
  $wm = (Invoke-DbaQuery -SqlInstance $central -Database $db -Query "
      SELECT ISNULL(w.last_instance_id,0) AS wm FROM agent.DimServer d
      LEFT JOIN agent.LoadWatermark w ON w.server_key = d.server_key WHERE d.server_name = '$s'").wm

  $rows = Invoke-DbaQuery -SqlInstance $s -Database msdb -As DataTable -Query "
      SELECT @@SERVERNAME AS server_name, h.instance_id, h.job_id, j.name AS job_name, h.step_id, h.step_name,
             h.run_status, h.run_date, h.run_time, h.run_duration, h.retries_attempted,
             LEFT(h.message, 4000) AS message
      FROM dbo.sysjobhistory h JOIN dbo.sysjobs j ON j.job_id = h.job_id
      WHERE h.instance_id > $wm"

  if ($rows.Rows.Count -gt 0) {
    Write-DbaDbTableData -SqlInstance $central -Database $db -Schema agent -Table StageJobHistory -InputObject $rows -AutoCreateTable
    Invoke-DbaQuery -SqlInstance $central -Database $db -Query "EXEC agent.usp_MergeJobHistory @server = '$s'"
  }
}

agent.usp_MergeJobHistory converts run_date/run_time/run_duration (see the conversion cheat sheet), upserts DimJob, inserts into FactJobRun where the key doesn't exist, updates the watermark to the max loaded instance_id, and truncates the stage rows for that server.

#Schedule

Run the collector more often than the shortest history retention on any server. Hourly is typical. Missing a load is harmless because the watermark catches up, as long as msdb hasn't purged the rows yet.

#Useful queries

Duration trend per job, by week:

SELECT j.job_name, DATEPART(ISO_WEEK, f.run_start) AS wk,
       AVG(f.duration_sec) / 60.0 AS avg_min, MAX(f.duration_sec) / 60.0 AS max_min
FROM agent.FactJobRun f JOIN agent.DimJob j ON j.job_key = f.job_key
WHERE f.step_id = 0 AND f.run_status = 1 AND f.run_start >= DATEADD(MONTH, -6, SYSDATETIME())
GROUP BY j.job_name, DATEPART(ISO_WEEK, f.run_start);

Failure rate per server, last 30 days:

SELECT s.server_name, COUNT(*) AS runs,
       SUM(CASE WHEN f.run_status = 0 THEN 1 ELSE 0 END) AS failures
FROM agent.FactJobRun f JOIN agent.DimServer s ON s.server_key = f.server_key
WHERE f.step_id = 0 AND f.run_start >= DATEADD(DAY, -30, SYSDATETIME())
GROUP BY s.server_name ORDER BY failures DESC;

#Housekeeping

• Columnstore on FactJobRun once it passes a few million rows.
• Keep messages for 90 days and durations forever, if storage is a concern.

Tip

Keep it boring: one stage table, one merge procedure, one collector. The value is in the history, not in the pipeline.