SQL Server : job history group by execution

Question

Duplicate of SQL Server jobs history - job execution id? since there's no solution yet

I need to replicate the SSMS job history. Showing only Job name, execution date, status and duration

Text

So far, tables like sysjobhistory have no information on how to group instances by execution.

And then there's the sysjobactivity table:

Text

I just don't get why the values are NULL sometimes

Is there a way to solve this?

Did you look at the solution given in the actual question that you link? (i.e. not posted as an answer) — Martin Smith, Jan 13 '22 at 10:05

Alex · Accepted Answer · 2022-01-13T12:50:39.340

It seems that people always forget that SSMS does not have some special/magical insight into SQL Server.

You can find out how SSMS obtains all of the information by starting a profiler and opening Job History window.

Cut down SQL code (obtained from Profiler) is below:

declare @tmp_sp_help_jobhistory table( ..... )

insert into @tmp_sp_help_jobhistory 
exec msdb.dbo.sp_help_jobhistory 
    @job_id = '9c6901d0-b325-4d14-bd88-22bb50c323af',
    @mode='FULL' 

SELECT
    tshj.instance_id AS [InstanceID],
    tshj.sql_message_id AS [SqlMessageID],
    tshj.message AS [Message],

.....

FROM @tmp_sp_help_jobhistory as tshj
ORDER BY [InstanceID] ASC

Looking at the code of sp_help_jobhistory you will see that it calls sp_help_jobhistory_full. The SQL code is quite simple there.

Looking at the output carefully you will notice that Jobs always start at Step 1 and end at Step 0 (summary details). To generate groups numbers:

WITH JobData AS(
    SELECT sjh.instance_id, -- This is included just for ordering purposes
     sj.job_id,
     sj.name AS job_name,
     sjh.step_id,
     sjh.step_name,
     sjh.sql_message_id,
     sjh.sql_severity,
     sjh.message,
     sjh.run_status,
     sjh.run_date,
     sjh.run_time,
     sjh.run_duration,
     sjh.retries_attempted,
     sjh.server,
     -- Every Job has Step 1 (starting step) and Step 0 (Job outcome details)
     -- Calculate Groups based on this
     ROW_NUMBER() OVER ( PARTITION BY step_id ORDER BY instance_id ) AS StepRn
    FROM msdb.dbo.sysjobhistory AS sjh
        INNER JOIN msdb.dbo.sysjobs_view AS sj ON sj.job_id = sjh.job_id
),
JobDataWithGroups AS(
    SELECT a.*, FirstStep.StepRn AS JobRunNumber
    FROM JobData AS a
        -- Get First Step Group Number
        CROSS APPLY( SELECT TOP 1 StepRn FROM JobData AS FirstStep WHERE FirstStep.instance_id <= a.instance_id AND FirstStep.step_id = 1 ORDER BY instance_id DESC ) AS FirstStep
)
SELECT *
FROM JobDataWithGroups
ORDER BY instance_id

Output

Interesting approach. Didn't know about the profiler. So this solution works when we filter with step_id = 0. Thanks — Wildfire, Jan 13 '22 at 12:58

SQL Server : job history group by execution

1 Answers1