A little while back I was doing some research into a failed job and ran into a slight problem. The Agent history settings were such that I was only seeing the last 2-3 runs of the job. This job is run “on demand” and I really wanted to see the last 10 runs or so. No help for it though, the history no longer existed unless I was willing to start restoring old copies of MSDB. It did however get me looking at the history settings for SQL Agent.

The history settings were set to the default of 1000 lines for the log as a whole and 100 lines for the individual jobs. It’s important to remember here that if you have a job that runs 4 steps that is 5 lines total, one line for the job and one line for each of the steps that runs. My system had more than 30 jobs which frequently had 10-20 steps each. If you multiply that out it you can see how you could get over 1000 lines total fairly quickly. For the future I changed the lines per job to 200 and changed the max total to 10,000 lines. That was a larger total than I really needed but it will allow me to add additional jobs without worrying about losing history information.

Here are a couple of general possibilities for you to consider.

There is an option for keeping only information younger than a certain date. This is a great option if all of your jobs have a fairly uniform schedule. However if you have different schedules, say a daily schedule and a weekly schedule, then this option is going to have problems. If you keep 2 weeks worth of history then you have 14 entries for the daily jobs, and 2 for the weekly jobs. If you keep 5 weeks of history your weekly jobs have 5 entries but your daily have 35. It gets worse of course when you add in monthly, quarterly or yearly jobs.

Frequently there are a wide variety of schedules and the “Remove agent history” option really isn’t one for these cases. So how should we set the “Limit size of job history log” settings? Start with three important values. The number of jobs, the minimum number of runs you want to see in history, and the largest number of steps that actually run on your biggest job (this one can generally be fudged down a bit). Then use the following formulas.

Max job history rows per job: Runs * (Max Steps + 1)

Max job history log size: Jobs * Runs * (Max Steps + 1) * 1.5

This “rows per job” setting is now big enough to cover your largest job. You will have extra runs in your smaller jobs but that isn’t generally a problem. The overall log size is now big enough to cover all of your jobs and leaving plenty of extra space for future jobs. Personally I would tend to round up a bit as well. 4331 for example would be an odd number and I would tend to make it 4500 or something like that. Yes I realize I’m suggesting keeping more history than you really need. However the size of each row in the sysjobhistory table is at most right around 4.5KB so keeping 10k rows is only around 45MB. That’s pretty small really. The worst thing that is likely to happen is it takes a bit longer to bring up the job history viewer.

Of course when you have some jobs with one or two steps and some with 20 or more you end up with the same type of discrepancy you had with the daily and weekly jobs where some jobs have a 5 or 6 runs worth of data and others have 50 or 60. At that point you just have to pick what sounds best to you.

Either way check your settings and make sure you have plenty of history now rather than waiting until after you start researching one of your jobs.

Filed under: Microsoft SQL Server, Problem Resolution, SQL Agent Jobs, SQLServerPedia Syndication, SSMS Tagged: microsoft sql server, problem resolution, SQL Agent Jobs, SSMS