Job Duration Anaylsis

DECLARE @job_name SYSNAME
SET @job_name = 'my job name'

SELECT COUNT(*) [run_count]
     , MIN((run_duration / 10000 ) * 3600 + ((run_duration / 100 ) % 100) * 60 + run_duration % 100) / 3600.0 [min_in_hour]
     , AVG((run_duration / 10000 ) * 3600 + ((run_duration / 100 ) % 100) * 60 + run_duration % 100) / 3600.0 [average_in_hour]
     , MAX((run_duration / 10000 ) * 3600 + ((run_duration / 100 ) % 100) * 60 + run_duration % 100) / 3600.0 [max_in_hour]
FROM msdb.dbo.sysjobhistory
WHERE job_id  = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @job_name)
  AND step_id = 0

SELECT run_date
     , (run_duration / 10000 ) * 3600 + ((run_duration / 100 ) % 100) * 60 + run_duration % 100 [duration_in_second]
FROM msdb.dbo.sysjobhistory
WHERE job_id  = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @job_name)
  AND step_id = 0
ORDER BY run_date DESC
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s