https://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/ The following query gives us the details of last/latest execution of the job step. This information can also be found in the Job History/Log File Viewer windows in SSMS. SELECT [sJOB].[job_id] AS [JobID] , [sJOB].[name] AS [JobName] , [sJSTP].[step_uid] AS [StepID] , [sJSTP].[step_id] AS [StepNo] , [sJSTP].[step_name] AS [StepName] , CASE [sJSTP].[last_run_outcome] WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 5 THEN 'Unknown' END AS [LastRunStatus] , STUFF( STUFF( RIGHT( '000000' + CAST( [sJSTP].[last_run_duration] AS VARCHAR( 6 )), 6 ) , 3, 0, ':' ) , 6, 0, ':' ) AS [LastRunDuration (HH:MM:SS)] , [sJSTP].[last_run_retries] AS [LastRunRetryAttempts] , CASE [sJSTP].[last_run_date] WHEN 0 THEN NULL ELSE CAST( CAST( [sJSTP].[last_run_date] AS CHAR( 8 )) + ' ' + STUFF( STUFF( RIGHT( '000000' + CAST( [sJSTP].[last_run_time] AS VARCHAR( 6 )), 6 ) , 3, 0, ':' ) , 6, 0, ':' ) AS DATETIME ) END AS [LastRunDateTime] FROM [msdb].[dbo].[sysjobsteps] AS [sJSTP] INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB] ON [sJSTP].[job_id] = [sJOB].[job_id] ORDER BY [JobName], [StepNo]
