Trend SQL Job duration - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trend SQL Job duration

Author

Topic

petek
Posting Yak Master

192 Posts

Posted - 2011-10-11 : 07:08:53

Hi Hope you can help; i need to trend the Job duratioin for a given SQL Agent Job.....is this possible i know the sp_help_jobhistory hold the info of duration but need to run it for a certain step...

thanks for looking...
Cheers

Pete

Kind Regards

Pete.

petek
Posting Yak Master

192 Posts

Posted - 2011-10-11 : 07:14:52

found somethig :

SELECT
CONVERT(DATETIME, RTRIM(jh.run_date)) + (jh.run_time * 9 + jh.run_time % 10000 * 6 + jh.run_time % 100 * 10) / 216e4 AS RunDateTime,
j.name AS JobName,
jh.step_id AS StepID,
SUBSTRING(RIGHT('000000' + CONVERT(varchar(6), jh.run_duration), 6), 1, 2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(varchar(6), jh.run_duration), 6), 3, 2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(varchar(6), jh.run_duration), 6), 5, 2) AS StepRunTime,
jh.step_name AS StepName
FROM msdb..sysjobhistory jh
INNER JOIN msdb..sysjobs j ON jh.job_id = j.job_id
where jh.step_id = 0
and j.name = 'JobName'
ORDER BY jh.run_date, jh.run_time, j.job_id, jh.step_id

Kind Regards

Pete.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources