Here’s a quick mystery. You have a SQL Server with several jobs that appear to be running properly and on schedule, but many of them show no history. You look around, and there doesn’t seem to be a nightly job history purge (you’re looking for something with “sp_purge_jobhistory“). So where is the record of these successful job runs?
Hm. Well, let’s take a quick look at the sysjobhistory table:
SELECT * FROM msdb..sysjobhistory;
Huh…that returned over 990 rows! So SOME jobs are showing history. But my Full backup ran successfully last weekend, and the record’s not in here!
The answer is simple, if a tiny bit obscured: Your job history log is set to only store a certain amount of information – in this particular case, 1,000 rows. And we’re running a LOT of jobs frequently, every day. That pushes out the old information.
Just resize your job history log (right click SQL Agent, Properties, History) to keep the amount of history you want.
Happy days,
Jen McCown
