If you are 100% sure that the job configured with SQL Server Agent has been executed successfully but you do not see the execution record when the Job History is opened, what could be the reason?
This is something I noticed yesterday with one of companies I consult. The client has many jobs configured with SQL Server Agent and all work fine without any issue. When the job history of the job is viewed using View History context menu, for some jobs, it shows the history but not for all. If the jobs have been successfully executed, it means that execution records should be exist in the msdb database as it is the database that stores all these information. So, we immediately checked the database;
USE msdb; GO select * from sysjobhistory;
It returned only 1000 records, means it maintains only thousand records. This is based on a setting of SQL Server Agent which is called Current job history log size (in rows). When it was checked, the setting was something like below;
This setting deletes all old records to make sure that it maintains only 1000 records. That is the reason we see the history for some records, not for all. What we saw from the above query is, details of jobs executed recently.
Best is, select Remove agent history checkbox and set to 4 weeks or more without using Limit size of job history log. This makes sure that we see the history for last one month. However, if the msdb database is getting larger, then you need to configure a lower value.
No comments:
Post a Comment