August 12, 2011 at 10:39 am
Word of warning : if the history has been purged/deleted then sysjobhistory will not display the same data as "Job Activity Monitor" i.e. "Job Activity Monitor" will display a last run date but this is not obtainable from running queries against sysjobhistory where the history has been purged because it just won't exist.
Use sysjobactivity to obtain the "real" last run date and to be in agreement with "Job Activity Monitor".
Try it; create a test job, run it a few times, manually delete the latest history records then run your query using sysjobhistory. You will find that your queries report an earlier last run time than SQL's "Job Activity Monitor".
Does it matter ? Well it may do, I noticed this problem when deciding which jobs could be deleted. It seemed that some jobs were never run but this was not the case as the history had been deleted.
February 1, 2013 at 4:53 pm
Do you have the script please to use sysjobactivity?
I would like to bring all the jobs with their names, steps, and the status, last run time, even if there is no history, or the jobs is disabled, not scheduled
June 18, 2014 at 9:28 pm
Bringing a thread back from the dead but to help people that stumble accross this page in the future
you could use the sql system function msdb.dbo.agent_datetime(run_date, run_time) to calculate the date time value from the run_date and run_time integer values stored in the msdb.dbo.sysjobhistory table.
e.g.
Select J.name,MAX(msdb.dbo.agent_datetime(run_date, run_time)) as 'LastLogRun'
FROM msdb.dbo.sysjobhistory H
Inner join msdb.dbo.sysjobs J ON H.job_id = J.job_id
where step_id = 0
GROUP BY J.name
June 27, 2018 at 2:26 pm
This returns runtime information:
SELECT
CAST(STR(h.run_date, 8, 0) AS DATE) AS StartDate
, SUBSTRING(CAST(CAST(CAST(STR(h.run_date, 8, 0) AS DATETIME)
+ CAST(STUFF(STUFF(RIGHT('000000'
+ CAST (h.run_time AS VARCHAR(6)), 6),
5, 0, ':'), 3, 0, ':') AS DATETIME) AS TIME) AS VARCHAR(16)),
1, 8) AS StartTime
, STUFF(STUFF(REPLACE(STR(run_duration, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0,
':') AS Run_Duration
, j.name JobName
, h.step_id StepNumber
, h.step_name StepName
, CAST(STR(h.run_date, 8, 0) AS DATETIME)
+ CAST(STUFF(STUFF(RIGHT('000000' + CAST (h.run_time AS VARCHAR(6)), 6), 5,
0, ':'), 3, 0, ':') AS DATETIME) AS StartDatetime
, DATEADD(SECOND,
( ( h.run_duration / 1000000 ) * 86400 ) + ( ( ( h.run_duration
- ( ( h.run_duration
/ 1000000 )
* 1000000 ) )
/ 10000 ) * 3600 )
+ ( ( ( h.run_duration - ( ( h.run_duration / 10000 ) * 10000 ) )
/ 100 ) * 60 ) + ( h.run_duration - ( h.run_duration / 100 )
* 100 ),
CAST(STR(h.run_date, 8, 0) AS DATETIME)
+ CAST(STUFF(STUFF(RIGHT('000000'
+ CAST (h.run_time AS VARCHAR(6)), 6), 5,
0, ':'), 3, 0, ':') AS DATETIME)) AS EndDatetime
, CASE h.run_status
WHEN 0 THEN 'failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS ExecutionStatus
, h.message MessageGenerated
FROM
msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
WHERE
h.step_id > 0 -- eliminates job total information row
ORDER BY
StartDate ASC
, StartTime ASC;
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply