Check your SQL Agent history settings before it’s too late!

6

February 10, 2014 by Kenneth Fisher

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.

SQLAgentHistory1

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.

6 thoughts on “Check your SQL Agent history settings before it’s too late!

  1. dave says:

    sysjobhistory retention, by default, does not offer much in the way of customization (ie, some jobs maintain no history because they run every minute, some retain all history b/c they are more important). I wrote my own process where you can customize retention based on various factors. Might be worth checking out: http://www.davewentzel.com/content/blocking-and-contention-sysjobhistory

  2. Paul Holden says:

    “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.” Actually there is one more line, for the result of the job, so that it is actually number of steps + 2.

  3. […] Now, be warned. The report and the history view both pull from the system views and they only hold so much history. You need to make sure that you are aware of what your job history system settings are. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,148 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: