Script to get Jobs Execution Details

Script to get Jobs Execution Details.

This script will give you whole execution details with job started time, job execution time, job finish time, and job execution status.

Note: For multiple scheduled jobs it will fetche only last executed job details.

You need to create one function for that with name “CONV_DATETIME”.

USE [MSDB]
GO
CREATE FUNCTION [DBO].[Conv_datetime](@Id INT,
                                      @It INT)
RETURNS DATETIME
AS
  BEGIN
      DECLARE @Vt CHAR(6),
              @D  CHAR(8),
              @Dt DATETIME

      SELECT
        @D = CONVERT(VARCHAR, @Id)

      IF Len(@It) = 5
        SET @Vt = '0' + CONVERT(CHAR, @It)
      ELSE IF Len(@It) = 4
        SET @Vt = '00' + CONVERT(CHAR, @It)
      ELSE IF Len(@It) = 3
        SET @Vt = '000' + CONVERT(CHAR, @It)
      ELSE IF Len(@It) = 2
        SET @Vt = '0000' + CONVERT(CHAR, @It)
      ELSE IF Len(@It) = 1
        SET @Vt = '00000' + CONVERT(CHAR, @It)

      SELECT
        @Dt = LEFT (@D, 4) + '-' + Substring(@D, 5, 2) + '-'
              + RIGHT(@D, 2) + ' ' + LEFT(@Vt, 2) + ':'
              + Substring(@Vt, 3, 2) + ':' + RIGHT(@Vt, 2)

      RETURN ( @Dt )
  END 

Execute below script after creating the function.

USE MSDB
GO
;WITH JOBSDETAIL
     AS (SELECT
           Row_number()
             OVER(
               PARTITION BY Sj.Name
               ORDER BY Run_time DESC) AS Rnk,
           Sj.Name,
           Run_date,
           Run_time,
           Run_duration                AS Runduration,
           CASE
             WHEN Run_status = 0 THEN 'Failed'
             WHEN Run_status = 1 THEN 'Successes'
             WHEN Run_status = 3 THEN 'Stopped'
           END                         AS Status
         FROM
           DBO.Sysjobhistory Sjh
         JOIN   DBO.Sysjobs Sj
           ON Sjh.Job_id = Sj.Job_id
         WHERE
          Sjh.Step_id = 0
          AND Run_date       = CONVERT(VARCHAR(8), Getdate(), 112))
SELECT
  Name                                                                                                             AS Jobname,
  CONVERT(VARCHAR(5), MSDB.DBO.Conv_datetime(Run_date, Run_time), 108)                                             AS Startdate,
  CONVERT(VARCHAR(5), Stuff(Stuff(RIGHT('000000' + CONVERT(VARCHAR, Runduration), 6), 5, 0, ':'), 3, 0, ':'), 108) AS Runduration,
  CONVERT(VARCHAR(5), Dateadd(SS, ( CASE Len(Runduration)
                                      WHEN 1 THEN Runduration
                                      WHEN 2 THEN Runduration
                                      WHEN 3 THEN ( Cast(LEFT(RIGHT(Runduration, 3), 1) AS INT) * 60 ) + ( RIGHT(Runduration, 2) )
                                      -- min,sec
                                      WHEN 4 THEN ( Cast(LEFT(RIGHT(Runduration, 4), 2) AS INT) * 60 ) + ( RIGHT(Runduration, 2) )
                                      -- min,sec
                                      WHEN 5 THEN ( Cast(LEFT(RIGHT(Runduration, 5), 1) AS INT) * 3600 ) + ( Cast(LEFT(RIGHT(Runduration, 4), 2) AS INT) * 60 ) + RIGHT(Runduration, 2)
                                      WHEN 6 THEN ( Cast(LEFT(RIGHT(Runduration, 6), 2) AS INT) * 3600 ) + ( Cast(LEFT(RIGHT(Runduration, 4), 2) AS INT) * 60 ) + RIGHT(Runduration, 2)
                                    END ), MSDB.DBO.Conv_datetime(Run_date, Run_time)), 108)                       AS Completedat,
  Status,
  Run_date
FROM
  JOBSDETAIL
WHERE
  Rnk = 1 

Regards,
Nirav Gajjar