July 31, 2009 at 10:41 am
I would like to query the durations of steps in several SQL Agent jobs and write them on a administrative report. Does anyone know how or where to get these values without view the history?
thanks in advance!
bsivel
July 31, 2009 at 11:18 am
The core table for this is msdb.dbo.sysjobhistory. You can add a great deal of information by joining to other metadata tables in msdb, but this will get you started:
SELECT SJ.[name], SJH.[step_id], SJH.[step_name], SJH.[run_date],
SJH.[run_time], SJH.[run_duration]
FROM msdb.dbo.[sysjobs] SJ LEFT JOIN
msdb.dbo.[sysjobhistory] SJH ON SJ.[job_id] = SJH.[job_id]
ORDER BY SJ.[name], SJH.[run_date] DESC, SJH.[step_id]
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
July 31, 2009 at 3:30 pm
Thanks Tim - this is exactly what I need. Looks like the duration is in milliseconds. Does that sound right?
August 3, 2009 at 7:04 am
Believe it or not, it's actually needs to be parsed out. For example a value of 1442 translates to MM:SS.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply