When exactly scheduled jobs started and completed - SQL Server by serverku

Some days ago, I worked to get failed job detail and an alert for the same. I used some system tables from the msdb database to fetch the details for the same. And msdb.dbo.sysjobhistory is one of the table among them, which used in the script. But this table doesn't contain data which shows the date and time when the job exactly started and completed. There are some fields which help us to get the same details,

• run_date
• run_time
• run_duration

Let us run the query and check data from table,

USE msdb
GO

SELECT TOP 3 job_id, 
             run_date, 
             run_time, 
             run_duration 
FROM   msdb.dbo.sysjobhistory WITH (nolock) 
ORDER  BY run_date DESC, 
          run_time DESC 

To get these details in shape and more readable format as we will use run_date, run_time and run_duration and convert them to date time format to achieve Start and Finish datetime. We will also use one more system table dbo.sysjobs for the job name and following is the script and output too,

USE msdb 
GO 

SELECT TOP 3 
 sj.job_id                 AS JobId, 
 sj.name                   AS JobName, 
 run_date                  AS RunDate, 
 run_time                  AS Runtime, 
 run_duration              AS RunDuration, 
 CAST(
  CONVERT(CHAR(8), run_date, 112) + ' ' + 
  STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':')
  AS DateTime)     AS StartedAt,     
 (CONVERT (VARCHAR,
  (DATEADD(ss,
   (CASE LEN(run_duration)
    WHEN 1 
    THEN run_duration
    WHEN 2 
    THEN run_duration
    WHEN 3 
    THEN (CAST(Left(right(run_duration,3),1) as int)*60) 
     + (right(run_duration,2))
    WHEN 4 
    THEN (CAST(Left(right(run_duration,4),2) AS int)*60) 
     + (right(run_duration,2)) 
    WHEN 5 
    THEN (CAST(Left(right(run_duration,5),1) AS int)*3600) 
     + (CAST(Left(right(run_duration,4),2) AS int)*60) 
     + right(run_duration,2) 
    WHEN 6 
    THEN (CAST(Left(right(run_duration,6),2) AS int)*3600) 
     + (CAST(Left(right(run_duration,4),2) AS int)*60) 
     + right(run_duration,2)
     END ),
   CAST(
    CONVERT(CHAR(8), run_date, 112) + ' ' + 
    STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':')
   AS DateTime)))
  ,120))     AS CompletedAt  
 FROM   msdb.dbo.sysjobhistory sjh WITH (nolock) 
        INNER JOIN msdb.dbo.sysjobs sj WITH (nolock) 
               ON sjh.job_id = sj.job_id 
WHERE  sjh.step_id = 0 
ORDER  BY run_date DESC, 
          run_time DESC 

(Click on image to enlarge)


Here we have all the details which we need. Hope this help you. Stay tuned for more posts.