Mainly msdb.dbo.sysjobs and msdb.dbo.sysjobhistory contains all major information regarding the SQL Server job and its running status and history. We may directly query both objects and get the details or we may use SSMS and view SQL Server Job History.
Solution
Here is a simple TSQL to simplify the work:
Query
SELECT [job].[job_id] AS [JobID],
[job].[name] AS [JobName],
CASE
WHEN [jh].[run_date] IS NULL
OR [jh].[run_time] IS NULL THEN
NULL
ELSE
CAST(CAST([jh].[run_date] AS CHAR(8)) + ' '
+ STUFF(STUFF(RIGHT('000000' + CAST([jh].[run_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
END AS [LastRunDateTime],
CASE [jh].[run_status]
WHEN 0 THEN
'Failed'
WHEN 1 THEN
'Succeeded'
WHEN 2 THEN
'Retry'
WHEN 3 THEN
'Canceled'
WHEN 4 THEN
'Running' -- In Progress
END AS [LastRunStatus],
STUFF(STUFF(RIGHT('000000' + CAST([jh].[run_duration] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)],
[jh].[message] AS [LastRunStatusMessage]
FROM [msdb].[dbo].[sysjobs] AS [job]
LEFT JOIN
(
SELECT [job_id],
[run_date],
[run_time],
[run_status],
[run_duration],
[message],
ROW_NUMBER() OVER (PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC) AS RowNumber
FROM [msdb].[dbo].[sysjobhistory]
WHERE [step_id] = 0
) AS [jh]
ON [job].[job_id] = [jh].[job_id]
--WHERE [job].[name] = 'YourJobName' -- Replace 'YourJobName' with the desired job name
--WHERE job.job_id = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' --You can write the selected Job ID information here.
ORDER BY [LastRunDateTime] DESC;
Steps
To run a script on a Microsoft SQL Server using SSMS.
- Open Microsoft SQL Server Management Studio
- Select [New Query] from the toolbar
- Copy the ‘Example Query’
- Select the database to run the query against
- Paste the ‘Example Query’ into the query window
- Click the [Execute] button
Result
