SQL Server Agent Schedule Decoder

  • Kinda weird, I do not have the table msdb.dbo.sysschedules...I can see sysjobschedules though.

    Did I not install SQL Server [Agent] correctly?

  • Rudy Panigas (1/6/2010)


    Excellent script! Keep up the great work!

    Question for you. Would you know if you can display the length of each job too? I would be nice to see how long the job took to complete from the start time.

    This would make a good addition and you could then collect this information from all your server to ensure of jobs are overlapping in their execution.

    Rudy

    How about this script:

    select\tj.job_id, Replace(j.name, '''', '') As [name],\t\tConvert(DateTime, CONVERT(VARCHAR, jh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':')) AS [run_start], CASE len(jh.run_duration) WHEN 1 THEN cast('00:00:0' + cast(jh.run_duration as char) as char (8)) WHEN 2 THEN cast('00:00:' + cast(jh.run_duration as char) as char (8)) WHEN 3 THEN cast('00:0' + Left(right(jh.run_duration,3),1) +':' + right(jh.run_duration,2) as char (8)) WHEN 4 THEN cast('00:' + Left(right(jh.run_duration,4),2) +':' + right(jh.run_duration,2) as char (8)) WHEN 5 THEN cast('0' + Left(right(jh.run_duration,5),1) +':' + Left(right(jh.run_duration,4),2) +':' + right(jh.run_duration,2) as char (8)) WHEN 6 THEN cast(Left(right(jh.run_duration,6),2) +':' + Left(right(jh.run_duration,4),2) +':' + right(jh.run_duration,2) as char (8)) END as [run_duration],\t\tDateAdd(second, jh.run_duration, Convert(DateTime, CONVERT(VARCHAR, jh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000'

    + CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':'))) AS [run_end],\t\tjh.message,\t\tjh.run_status from msdb..sysjobs j (nolock) inner join msdb..sysjobhistory jh (nolock) on jh.job_id = j.job_id where jh.step_id = 0 order by j.name, jh.run_date desc

  • Rudy Panigas (1/6/2010)


    Excellent script! Keep up the great work!

    Question for you. Would you know if you can display the length of each job too? I would be nice to see how long the job took to complete from the start time.

    This would make a good addition and you could then collect this information from all your server to ensure of jobs are overlapping in their execution.

    Rudy

    Wow, couldn't read my first reply, try this one:

    select j.job_id,

    Replace(j.name, '''', '') As [name],

    Convert(DateTime, CONVERT(VARCHAR, jh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':')) AS [run_start],

    CASE len(jh.run_duration)

    WHEN 1 THEN cast('00:00:0' + cast(jh.run_duration as char) as char (8))

    WHEN 2 THEN cast('00:00:' + cast(jh.run_duration as char) as char (8))

    WHEN 3 THEN cast('00:0' + Left(right(jh.run_duration,3),1) +':' + right(jh.run_duration,2) as char (8))

    WHEN 4 THEN cast('00:' + Left(right(jh.run_duration,4),2) +':' + right(jh.run_duration,2) as char (8))

    WHEN 5 THEN cast('0' + Left(right(jh.run_duration,5),1) +':' + Left(right(jh.run_duration,4),2) +':' + right(jh.run_duration,2) as char (8))

    WHEN 6 THEN cast(Left(right(jh.run_duration,6),2) +':' + Left(right(jh.run_duration,4),2) +':' + right(jh.run_duration,2) as char (8))

    END as [run_duration],

    DateAdd(second, jh.run_duration, Convert(DateTime, CONVERT(VARCHAR, jh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':'))) AS [run_end],

    jh.message,

    jh.run_status

    from msdb..sysjobs j (nolock)

    inner join msdb..sysjobhistory jh (nolock) on jh.job_id = j.job_id

    where jh.step_id = 0

    order by j.name, jh.run_date desc

  • Wow. Works nice. When I have some time, I'll see about combining the 2 scripts so that you get a report that displays the combined data. This would make an excellent SSRS report for DBAs.

    Very nicely done!

    Rudy

    Rudy

  • This is great....Thanks for the script 🙂

  • Glad you like it!

    Enjoy!

Viewing 6 posts - 16 through 20 (of 20 total)

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