unable to see job history

Question

Hi All,

I am unable to see job history for some jobs. Usually, we run some maintenance jobs. for example) index maintenance and updating stats. Why it is not showing up? anything needs to be changed in SQL Agent Properties?

Thanks,

Sam

Answer 1

Hi,

Surely you need to increase maximum job history log size (in rows) or alternatively you can uncheck "Limit size of job history log" and check "Remove agent history" & then select number days/weeks/months per your requirement (how long you need to have history).

Regards,

Answer 2

Hi Samantha r •,

If you are unable to see job history, you can check if the SQL Server Agent service is running (which seems to be the case) and if the job has been executed at least once. Please check if restart of agent service helps.

Please check if you find any error related to SQL Server Agent in the log.

https://learn.microsoft.com/en-us/sql/ssms/agent/view-sql-server-agent-error-log-sql-server-management-studio?view=sql-server-ver15#to-view-the--agent-error-log

An alternative way to view job history is to query the msdb.dbo.sysjobhistory table.

You can use the following query to retrieve job history:

SELECT * FROM msdb.dbo.sysjobhistory WHERE job_id = '<job_id>'

or below scripts to get the job history-

select

 j.name as 'JobName',

 run_date,

 run_time,

 msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime'

From msdb.dbo.sysjobs j

INNER JOIN msdb.dbo.sysjobhistory h

 ON j.job_id = h.job_id

where j.enabled = 1  --Only Enabled Jobs

order by JobName, RunDateTime desc

MSDB.dbo.sysjobhistory Table and run_duration Column'

select

j.name as 'JobName',

run_date,

run_time,

msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',

run_duration

From msdb.dbo.sysjobs j

INNER JOIN msdb.dbo.sysjobhistory h

ON j.job_id = h.job_id

where j.enabled = 1 --Only Enabled Jobs

order by JobName, RunDateTime desc

Thank you!