Not able to view the history of any of the jobs in SQL Server

Question

Hi Microsoft & Experts,

Not able to view the history of any of the jobs in SQL Server.

SQL Server details: SQL Server 2014 SP3-CU-GDR

1) SQL Server agent properties looks like this:
[Tried changing these numbers to a higher value and did a SQL Agent restart – No help!]

2) MSDB is has enough space and the “sys.jobhistory” does not have any data in it.
3) Verified that “sp_purge_jobhistory” is not run manually or automatically.
4) “Job Steps Execution History” under reports also shows nothing.

I have also referred other blogs for help, but issue still not able to view the job history.
Please suggest on what could be the reason for the mentioned issue.

Thanks in advance!

Answer 1

Hi @TheDBLearningGuy ,

To begin with, use the below script to check the amount of each job history

 SELECT j.name, COUNT(*) Executions  
 FROM msdb.dbo.sysjobs j  
 INNER JOIN msdb.dbo.sysjobhistory h  
 ON j.job_id = h.job_id  
 GROUP BY j.name  
 ORDER BY Executions DESC  

If there are results returned but you can't see any record in the UI, it should be a problem with SSMS, then you should download the latest SSMS.
SSMS 18.10 is the latest one: https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You likely have a job which is running every 1 min causing the log to remove old entries.

In the Job history viewer, click the checkbox next to "Job History" and see what it shows.

Answer 3

Just to cover the unlikely, but this SELECT comes back empty, doesn't it:

SELECT object_name(parent_id, db_id('msdb')), * 
FROM msdb.sys.triggers
WHERE is_ms_shipped = 0
ORDER BY 1

Answer 4

Hi @TheDBLearningGuy ,

You can double check if a job calls the sp_purge_jobhistory stored procedure with the following script:

SELECT b.[name],CASE WHEN b.enabled=1 THEN 'enable' ELSE 'disable' END   
,b.description,a.step_name,a.command   
FROM msdb.dbo.sysjobsteps a INNER JOIN   
msdb.dbo.sysjobs b ON a.job_id=b.job_id  
WHERE a.command LIKE '%sp_purge_jobhistory%'  

In addition, > ran multiple times it doesn't show up its execution count.
Do you mean after multiple runs, no results are returned? So is there a record for a short-lived existence?
So that's why I want you to execute the above script to check.

Best regards,
Seeya

Answer 5

Take two on the triggers: you could add a DELETE trigger on sysjobhistory that logs deletions to a table where you record the time, the user, app_name() and host_name(). That could maybe give you some idea of what is going on.

I should add that this is certainly not supported to do this, so be careful!