Checking SQL Server Job History by Script

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