SQL Server Agent Schedule Decoder

Question

Post reply

SQL Server Agent Schedule Decoder

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

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

andegre SSCertifiable Points: 5875 More actions · Answer 1

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

Did I not install SQL Server [Agent] correctly?

andegre SSCertifiable Points: 5875 More actions · Answer 2

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

andegre SSCertifiable Points: 5875 More actions · Answer 3

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

Rudy Panigas SSChampion Points: 10702 More actions · Answer 4

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