May 12, 2014 at 2:20 am
Hey all,
I am querying the msdb.dbo.sysjobhistory. Some of the jobs have multiple steps and run hourly.
I cant see any entry on this for a run_id or similar? Am i missing something or can you not group these entries into scheduled runs?
Dan
May 12, 2014 at 5:56 am
Have you tried joining on the sysjobs and sysjobschedules tables (on job_id)
May 12, 2014 at 6:20 am
Yes, but that just gives you details of the jobs and the steps the job should take. How to then make it know that 5 steps that were run 2 hours ago were separate from the same 5 steps 10 minutes ago - i am not sure?
May 12, 2014 at 6:48 am
I have this script for SQL 2005 try this out
SELECT job.Name,
case job.description when 'No description available.' then '' else job.description end,
step.step_name,
case his.run_status when 0 then 'Failed' Else 'Success' end 'Status',
convert(varchar,convert(datetime,cast(his.run_date as varchar),106),106) 'Run Date',
case len(run_time) when 6 then SUBSTRING(convert(varchar,run_time),1,2) + ':' + SUBSTRING(convert(varchar,run_time),3,2) + ':' + SUBSTRING(convert(varchar,run_time),5,2)
when 5 then SUBSTRING(convert(varchar,run_time),1,1) + ':' + SUBSTRING(convert(varchar,run_time),2,2) + ':' + SUBSTRING(convert(varchar,run_time),4,2)
when 3 then '12' + ':0' + SUBSTRING(convert(varchar,run_time),1,1) + ':' + SUBSTRING(convert(varchar,run_time),2,2)
when 1 then '12:00:00'
else convert(varchar,run_time)
End 'Run Time',
his.run_duration,
case when his.sql_message_id <> 0 and his.sql_message_id <> 8153then his.message else '' end 'Message'
from msdb.dbo.SysJobs job,
msdb.dbo.SysJobSteps step,
msdb.dbo.SysJobHistory His
where job.enabled = 1
and job.job_id = step.job_id
and job.job_id = His.job_id
and step.step_id = His.step_id
and his.run_date = ( select top 1 his1.run_date from msdb.dbo.SysJobHistory his1
where his1.job_id = His.job_id
and step.step_id = His1.step_id
order by 1 desc
)
and his.run_time = ( select top 1 his2.run_time from msdb.dbo.SysJobHistory his2
where his2.job_id = His.job_id
and his2.run_date = his.run_date
and step.step_id = His2.step_id
order by 1 desc
)
and job.description not like 'This job is owned by a report server process%'
--and his.run_status = 0
order by his.run_status, job.Name, His.step_id
Regards
Durai Nagarajan
May 12, 2014 at 7:50 am
Each step has a unique id. Combine that with the run_time and you should have a unique running instance
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply