Job history not showing whther succeeds of fails

Question

I have a sql 2016 where jobs run correctly as expected on scheduel and I recieve alerts by email when it completes. However, when I right click the jon and chose "View History" nothing is displayed.
I doubted that there was something corrupt because of migration from sql2k12 (long time a ago), after googling, I found a thread where it indicated to drop and recreate which I did but still no history.
Thanks for your help

Answer 1

There are several reasons the job history would be empty. The main reason is rights. Only sysadmins and job owners can see history in the job history.

If it is not rights, then there is a retention period for job history and a max size of the log. If you have many jobs running it will fill the log and remove your job history.

See:
https://learn.microsoft.com/en-us/sql/ssms/agent/resize-the-job-history-log?view=sql-server-ver15

Answer 2

Hi @Salam Elias ,

Welcome to Microsoft Q&A!
I did a test:
First, let's take a look at the default configuration:

Under this setting, I can see the job history. For you to see more clearly, I executed the following script to view the number of each job.

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  

Results:

You can see that my number is far more than 200, and now I changed the maximum number to 200.

It will clear part of the log but not all of it. After all, if you set a maximum value, this value will not be 0.

So I ask my question, do you have any maintenance plan that contains the history of deleting this job.
Secondly, run select * from msdb.dbo.sysjobhistory
If you see a lot of records but you right-click to view it but nothing is displayed, I suggest you install the latest SSMS.

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 3

So mnay thank sfor the help and explanation, yes you were correct, it was an issue with default config as I have recently added some jobs that run every 5-10 minutes which filled the tables.
By the way, is there a possibility to configure a job not to write to history. I need to do this to one of the jobs setup by Biztalk server that runs every minute for which I don't need the history to be recorded as it is a clean job

Thanks again

Answer 4

Hi @Salam Elias ,

There are two method. Let me give you an overview.
1. Use the UI
Delete the history of the specified job you want to delete.

2. Use sp_purge_jobhistory
The script is as follows:

USE msdb;  
GO  
EXEC dbo.sp_purge_jobhistory  
     @job_name = N'N1-Test1-1';  
GO  

Manual cleaning is the same as method one.
For convenience, you might think of creating a Clear job to accomplish this. Just set a good time for it and set how often to clean it up. But this clearjob will also have a history. So how to decide you depends on the amount of your actual history.
I think you can set the maximum number slightly larger, and then clean it up after a few hours or a break.

Best regards,
Seeya

Answer 5

OK thanks, so no possibility to configure a job to not write to history table :-)