I have a Log File Viewer which only displays job history of the current day. I have gone to the History under SQL Server Agent Properties to change the Maximum job history to 10000 from 1000 and still I am unable to view the history for more than one day. What can I do please?
How are you viewing the log files? Are you using the view history menu on SQL Server Agent -> Jobs, or are you using Management -> SQL Sever logs? It sounds like it is neither of these.
1 Like
I right-clicked on the job and click on view history but it only shows today's history even though the job has been running successfully for the past weeks.
If you change the setting today, that will take effect only going forward. Or, are you saying that you made this change more than a day ago, and still you are able to view only one day's data? If it is, check the following:
-
Is the "remove agent history" check box checked? If it is, set the "Older" than value to something that fits your needs.
-
How many records does the agent produce each day (not just from the job you are interested in, but in all jobs put together). You can see all the logs together if you right click on the Jobs node and select view history. If it is larger than the number you have set, increase that.
1 Like
I believe the problem is found in the #2 of your suggestion above. I found out that the number of records per day is greater than the maximum job history log size (in rows) which by default is 1000; so, I have changed that to 20000. I will check it again tomorrow to see if the problem is solved and get back to you. Thank you
We have "Limited size of job history log" and "Remove agent history" turned OFF (the later only does a one-off job, not a scheduled task 
)
We then have a separate scheduled job to purge Job History using
msdb.dbo.sp_purge_jobhistory
and
msdb.dbo.sp_delete_backuphistory
You could also Cycle the SQL Error Log and the log for SQL Agent:
msdb..sp_cycle_errorlog
msdb..sp_cycle_agent_errorlog