Post reply

SQL SERVER AGENT JOBS MSDB

  • February 28, 2017 at 7:19 am

    #335687

    Hi all,

    I'm trying to see what the difference in duration of backup jobs in msdb is in relation to the sql server agent. The current queries I have are as follows, these get me really close but it's not exactly what I want, any suggestions for how to possibly combine the two?

    select j.name
       ,jh.step_name
         ,msdb.dbo.agent_datetime(jh.run_date, jh.run_time) as 'Run Date & Time'
       ,jh.run_date
         ,jh.run_time
       ,jh.run_duration
    from msdb..sysjobhistory jh
    inner join msdb..sysjobs j
    on jh.job_id = j.job_id
    and jh.step_id = 0
    and j.enabled = 1
    order by j.name, jh.run_date, jh.run_time

    ---------------------------------------------------------

    select j.name
       , Max(msdb.dbo.agent_datetime(jh.run_date, jh.run_time)) as max_run_date
         , Min(msdb.dbo.agent_datetime(jh.run_date, jh.run_time)) as min_run_date
    from msdb..sysjobhistory jh
    inner join msdb..sysjobs j
    on jh.job_id = j.job_id
    and jh.step_id = 0
    and j.enabled = 1
    group by j.name
    order by j.name

  • March 1, 2017 at 6:17 am

    #1930940

    I'm a little confused on exactly what you are doing.  You seem to make a distinction between backup jobs in msdb and backup jobs in Agent.  That's the part I'm confused on.  Are you using a native maintenance plan to do your backups?

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

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