This project builds a data mart from the SQL Server Agent tables. It implements an increment load design and includes type 2 slowly changing dimension handling for jobs and job steps.
From docs: In most cases the data is updated only after the job step completes and the table typically contains no records for job steps that are currently in progress, but in some cases underlying processes do provide information about in progress job steps.
Incremental load columns: instance_id (but what happens if SQL Agent restarts?), run_date, run_time, retries_attempted
From docs: In most cases the data is updated only after the job step completes and the table typically contains no records for job steps that are currently in progress, but in some cases underlying processes do provide information about in progress job steps.
Incremental load columns: instance_id (but what happens if SQL Agent restarts?), run_date, run_time, retries_attempted
Use the code in docs https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobhistory-transact-sql to convert run_time and run_duration columns. runz_duration handles days.
Based on the incrementing instance_id, the job step completion row gets written before the job completion row. This makes sense.
If the SQL Agent service is restarted, the instance_id values keep incrementing as normal.