Reporting on SQL Agent history in 2005

  • Hopefully this is the right forum. It's fundamentally a T-SQL question.

    I'm trying to make a report that is useful for our network sysadmins to monitor the SQL Agent jobs from 23 different servers. They aren't keen on using SSMS to connect to many servers and manually check each job so I'm doing an exception report to try and make their lives easier.

    I'm nearly there in that I have the query, and it works, but there is too much detail so I want to collapse the steps into each job-run, but I have no way of grouping the job-runs.

    In the MSDB system database there are these useful tables:



    This is the query so far. To reproduce this, replace the GUID with the GUID of a job on your SQL Agent - which you can find in [msdb].[sysjobs] table. It makes the problem clearer if you pick a job with more than 1 step.

    DECLARE @job_id uniqueidentifier

    Set @job_id = '0F70F5A1-C169-4CB4-99D8-A16FC2ED32A0'

    SELECT sJOBH.job_id, AS job_title, sJOB.description, sJOBH.step_id, sJOBH.step_name, CASE WHEN [sJOBH].[run_date] IS NULL OR

    [sJOBH].[run_time] IS NULL THEN NULL ELSE CAST(CAST([sJOBH].[run_date] AS CHAR(8))

    + ' ' + STUFF(STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS DATETIME) END AS RunDateTime,

    STUFF(STUFF(RIGHT('000000' + CAST(sJOBH.run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS [RunDuration (HH:MM:SS)],

    CASE [sJOBH].[run_status] WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'Running' END AS LastRunStatus,

    sJOBH.retries_attempted, sJOB.date_modified AS last_modified, sJOBH.message

    FROM sysjobhistory AS sJOBH INNER JOIN

    sysjobs AS sJOB ON sJOBH.job_id = sJOB.job_id

    WHERE (sJOB.enabled = 1)

    AND (sJOBH.job_id = @job_id)

    ORDER BY job_title, RunDateTime, sJOBH.step_id

    I picked up this next query (from SQL Profiler) which appears to drive the SQL Agent job history screen in SSMS. It doesn't make it any clearer how to group the steps of a job into a single "session" i.e. for a particular job there are 9 steps in a job, step number 0 is the job level, the 9 steps that happen just after that are all part of that job.

    Note you also need to replace the GUID of this query with one from your system.

    declare @tmp_sp_help_jobhistory table


    instance_id int null,

    job_id uniqueidentifier null,

    job_name sysname null,

    step_id int null,

    step_name sysname null,

    sql_message_id int null,

    sql_severity int null,

    message nvarchar(4000) null,

    run_status int null,

    run_date int null,

    run_time int null,

    run_duration int null,

    operator_emailed sysname null,

    operator_netsent sysname null,

    operator_paged sysname null,

    retries_attempted int null,

    server sysname null


    insert into @tmp_sp_help_jobhistory

    exec msdb.dbo.sp_help_jobhistory

    @job_id = '0f70f5a1-c169-4cb4-99d8-a16fc2ed32a0',


    SELECT *,

    tshj.instance_id AS [InstanceID],

    tshj.sql_message_id AS [SqlMessageID],

    tshj.message AS [Message],

    tshj.step_id AS [StepID],

    tshj.step_name AS [StepName],

    tshj.sql_severity AS [SqlSeverity],

    tshj.job_id AS [JobID],

    tshj.job_name AS [JobName],

    tshj.run_status AS [RunStatus],

    CASE tshj.run_date WHEN 0 THEN NULL ELSE


    stuff(stuff(cast(tshj.run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' +

    stuff(stuff(substring(cast(1000000 + tshj.run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'),

    120) END AS [RunDate],

    tshj.run_duration AS [RunDuration],

    tshj.operator_emailed AS [OperatorEmailed],

    tshj.operator_netsent AS [OperatorNetsent],

    tshj.operator_paged AS [OperatorPaged],

    tshj.retries_attempted AS [RetriesAttempted],

    tshj.server AS [Server],

    getdate() as [CurrentDate]

    FROM @tmp_sp_help_jobhistory as tshj

    ORDER BY [InstanceID] ASC

    In the SSMS job history, it collapses each job run into the job level, and you can drill-down to see all the steps. This is the effect I want to reproduce.

    Without using a loop or a cursor (coz they are evil!), how is this done? They must have done this in code because there is no clear way to group the results in the dataset.

    There's an instance ID, but it varies for every step. There's a run-date, which would be fine if they were all daily jobs but some run more than once a day.

    Sorry for the long intro but basically I think I want to create another column that will group one run of all the steps of a job together, which I could then use to group in the report. Unless there's another option I'm missing. Any ideas? Thanks 🙂

  • Following code works, i use it in RS report for our helpdesk so they can monitor critical jobs running on different servers, basicaly they want to see job history for the day and how each step manage, of course i neded job execution ID for grouping...


    @job uniqueidentifier

    create table #job


    IDint identity (1,1),

    JobExecutionIDint null,



    step_enddatetime null,








    insert #job


    null, as job_name,

    CONVERT(DATETIME, RTRIM(h.run_date)) +

    (h.run_time * 9 + h.run_time % 10000 * 6 + h.run_time % 100 * 10) / 216e4 [occur],


    cast(RIGHT('000000' + CONVERT(varchar(6), h.run_duration), 6) as int) [run_duration] ,


    case h.run_status when 0 then 'Failed'

    when 1 then 'Succeeded'

    when 2 then 'Retry'

    when 3 then 'Canceled' end [Status]




    from msdb.dbo.sysjobhistory h (nolock)

    inner join msdb.dbo.sysjobs j (nolock) on h.job_id = j.job_id

    where j.job_id = @job

    and h.run_date = convert(varchar, getdate(), 112)

    --and h.step_id > 0

    order by h.run_time, h.step_id

    update #job

    set step_end = dateadd(second, run_duration, step_start)


    @row int,

    @step_id int,

    @last_id int,

    @execution_id int

    set @row = 1

    set @execution_id = 1

    set @last_id = -1


    select step_id from #job

    order by step_start, step_id

    OPEN #cur

    FETCH #cur into @step_id



    if @step_id >= @last_id


    update #job

    set JobExecutionID = @execution_id

    where ID = @row




    set @execution_id = @execution_id + 1

    update #job

    set JobExecutionID = @execution_id

    where ID = @row


    set @row = @row + 1

    set @last_id = @step_id

    fetch next from #cur into @step_id


    close #cur

    deallocate #cur

    select * from #job

    drop table #job

    PS. i know it's a bit late 🙂 but i didn't find any solution anywhere.

  • Thanks krysys for the suggestion.

    I ended up using a row_number() over (partition by) construct to achieve what I needed, I should have followed up.

    Thanks though.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply