Viewing Job History in SQL Server

Hello everyone,

In this article, I will try to give information about viewing Job history in SQL Server.

In SQL Server, in some cases, you may want to learn the working status of Jobs, that is, their history.

You can easily do this using the code below.

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.job_id = 'BD6586AB-0D27-4399-88FA-4D329C243D3C' --You can write the selected Job ID information here.
ORDER BY LastRunDateTime DESC;

Good luck to everyone in business and life.

30 Views

Yavuz Selim Kart

I try to explain what I know in software and database. I am still improving myself by doing research on many programming languages. Apart from these, I am also interested in Graphic Design and Wordpress. I also have knowledge about SEO and Social media management. In short, I am a determined person who likes to work hard.

You may also like...

Don`t copy text!