Checking SQL Server Job History by Script

426 views 13:31 0 Comments 7 November 2022

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.


Here is a simple TSQL to simplify the work:


SELECT [job].[job_id] AS [JobID],
       [job].[name] AS [JobName],
           WHEN [jh].[run_date] IS NULL
                OR [jh].[run_time] IS NULL THEN
               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
           WHEN 1 THEN
           WHEN 2 THEN
           WHEN 3 THEN
           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]
        SELECT [job_id],
               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;


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



Leave a Reply

Your email address will not be published. Required fields are marked *