We have several SQL Servers running SQL Agent jobs, recording history in their own sysjobhistory tables. I am attempting to set up one centralized server with a job that collects the history from all the other servers, formats it a bit, and puts it into a table called AllJobHistory. As part of this process, I would like one column to indicate that multiple steps of a job were part of the same job run. They are already marked as being part of the same job through the job_id column, but I want to know that specific rows came from the 3:00 run of the job versus the 4:00 run. Being able to filter based on this column would make troubleshooting jobs that much easier for us, but I don’t see anything that links those steps together in any existing system table or DMV, is there?
My first attempt at rolling my own was to use the run_date, run_time, and run_duration columns. For each step, if I subtract the total run_duration up to this point from the run_time, it should bring me back to a time that is unique compared to all other runs of this job. This looked like it was working until I found a case where it wasn’t (likely because SQL Server is rounding run_time and run_duration at the precision of seconds). Here is my attempt at the query (with extra columns removed).
WITH JobDetails AS ( SELECT QUOTENAME(UPPER('ServerName')) AS [Server], j.job_id AS [JobID], j.name AS [JobName], s.step_id AS [Step], msdb.dbo.agent_datetime(run_date, run_time) AS [RunDate], (run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100) AS [RunDurationSeconds] FROM msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id LEFT OUTER JOIN msdb.dbo.sysjobsteps s ON s.job_id = h.job_id AND s.step_id = h.step_id WHERE h.step_id != 0 ), GroupedDetails AS ( SELECT jd.[Server], jd.[JobID], jd.JobName, jd.Step, jd.RunDate, jd.RunDurationSeconds, DATEADD(SECOND, -ISNULL(SUM(jd.RunDurationSeconds) OVER (PARTITION BY jd.JobName ORDER BY jd.JobName, jd.RunDate, jd.Step ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0), jd.RunDate) AS grp FROM JobDetails AS jd ) SELECT gd.[Server], gd.JobName, gd.Step, gd.RunDate, gd.RunDurationSeconds, CONVERT(VARCHAR(36), gd.JobID) + '_' + FORMAT(gd.grp, 'yyyyMMdd_HHmmss') AS JobRunString FROM GroupedDetails AS gd;
So, is there any reliable way of linking the steps of a job run together in sysjobhistory?