I have a job which runs every night around 02:00 AM.

But I cannot see the job history for more than two days.Can you kindly help me on this.

The limit size is very high and we hardly have one job which runs each day.

Still the issue. Please advice.

enter image description here

  • Note that msdb.dbo.sysjobhistory has a row for each step executed (multiple rows if steps have to be retried, and possibly if you keep full step output in the table), plus a row for the job itself. How many rows are in msdb.dbo.sysjobhistory?
    – RDFozz
    Apr 5, 2017 at 14:52

1 Answer 1


SQL Server Agent stores the history of job execution in system tables in msdb database.

The following query gives us the details of last/latest execution of the SQL Server Agent Job and also the next time when the job is going to run (if it is scheduled). This information can also be found in the Job History/Job Activity Monitor windows in SSMS.

        [sJOB].[job_id] AS [JobID]
        , [sJOB].[name] AS [JobName]
        , CASE 
            WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
            ELSE CAST(
                    CAST([sJOBH].[run_date] AS CHAR(8))
                    + ' ' 
                    + STUFF(
                        STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)),  6)
                            , 3, 0, ':')
                        , 6, 0, ':')
                    AS DATETIME)
          END AS [LastRunDateTime]
        , CASE [sJOBH].[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([sJOBH].[run_duration] AS VARCHAR(6)),  6)
                    , 3, 0, ':')
                , 6, 0, ':') 
            AS [LastRunDuration (HH:MM:SS)]
        , [sJOBH].[message] AS [LastRunStatusMessage]
        , CASE [sJOBSCH].[NextRunDate]
            WHEN 0 THEN NULL
            ELSE CAST(
                    CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
                    + ' ' 
                    + STUFF(
                        STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),  6)
                            , 3, 0, ':')
                        , 6, 0, ':')
                    AS DATETIME)
          END AS [NextRunDateTime]
        [msdb].[dbo].[sysjobs] AS [sJOB]
        LEFT JOIN (
                        , MIN([next_run_date]) AS [NextRunDate]
                        , MIN([next_run_time]) AS [NextRunTime]
                    FROM [msdb].[dbo].[sysjobschedules]
                    GROUP BY [job_id]
                ) AS [sJOBSCH]
            ON [sJOB].[job_id] = [sJOBSCH].[job_id]
        LEFT JOIN (
                        , [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 [sJOBH]
            ON [sJOB].[job_id] = [sJOBH].[job_id]
            AND [sJOBH].[RowNumber] = 1
    ORDER BY [JobName];

I am also attaching the output of after execution of this script. enter image description here

For your ref Here and Here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.