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