How to extract start and end time of a job in SQL Server?

In Query analyzer

— prints job name, step name, start time and end time of each step in the job and sorts by latest on the top
— if jh.sql_message_id is 0 then that step failed but that status is set set at the overall job outcome
SELECT j.Name, jh.Step_name,
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/)
AS Start_DateTime,
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100) / (86399.9964 /* Start Date Time */)
+ ((jh.run_duration/10000 * 3600) + ((jh.run_duration%10000)/100*60) + (jh.run_duration%10000)%100
/*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime
from msdb..sysjobhistory jh, msdb..sysjobs j
where jh.job_id=j.job_id
and j.name = ‘Job_name’
ORDER BY run_date desc, run_time desc

Sample Output:

Job name Step Name Start Time End Time
job_name step_name 2009-05-13 12:55:01.000 2009-05-13 23:26:01.000
job_name (Job outcome) 2009-05-13 12:55:01.000 2009-05-13 23:26:01.000

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