Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

msdb.dbo.sysjobhistory Linking Expand / Collapse
Author
Message
Posted Monday, May 12, 2014 2:20 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 7:00 AM
Points: 759, Visits: 834
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
Post #1569709
Posted Monday, May 12, 2014 5:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:31 PM
Points: 280, Visits: 615
Have you tried joining on the sysjobs and sysjobschedules tables (on job_id)
Post #1569792
Posted Monday, May 12, 2014 6:20 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 7:00 AM
Points: 759, Visits: 834
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?
Post #1569807
Posted Monday, May 12, 2014 6:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
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
Post #1569824
Posted Monday, May 12, 2014 7:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:31 PM
Points: 280, Visits: 615
Each step has a unique id. Combine that with the run_time and you should have a unique running instance
Post #1569852
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse