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

Querying the duration of the latest successful SQL Agent Job step Expand / Collapse
Author
Message
Posted Friday, July 31, 2009 10:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 14, 2009 10:34 AM
Points: 3, Visits: 21
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
Post #763256
Posted Friday, July 31, 2009 11:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:55 AM
Points: 1,012, Visits: 440
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 MVP
http://www.sqlcruise.com
http://www.thesqlagentman.com
http://www.linkedin.com/in/timothyford
Post #763283
Posted Friday, July 31, 2009 3:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 14, 2009 10:34 AM
Points: 3, Visits: 21
Thanks Tim - this is exactly what I need. Looks like the duration is in milliseconds. Does that sound right?

Post #763414
Posted Monday, August 03, 2009 7:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:55 AM
Points: 1,012, Visits: 440
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 MVP
http://www.sqlcruise.com
http://www.thesqlagentman.com
http://www.linkedin.com/in/timothyford
Post #764027
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse