Save Enough Job History Before It’s Too Late

Let’s imagine a scenario. It’s one that I’ve been through and I suspect it’s one that some reading this post have been through as well.

You come in on Monday morning feeling good after a relaxing weekend. You check your e-mail and have an alert about a job that failed the previous Friday night. Because you’re a good DBA, you want to determine exactly why that job failed. Maybe it’s a fluke that requires no further action or maybe some intervention is needed. We’ll find out, right?

When opening job history for the job in question, you find no history exists.

Uh oh.

If it’s critical to know why the job failed, you can restore a backup of your msdb database and search that copy to track down the job history. That’s an option but not as good as having the job history readily available.

To reduce the likelihood of this issue happening again, it’s time to make some settings changes.

Don’t Know What You Got…

In SSMS, right click on SQL Server Agent under Object Explorer, select Properties, and select the History page:

Here we see the default settings for job history. The job history management is broken down a couple of ways.

I’d leave the first checkbox checked to limit the size of the job history log. Under that setting, we see a couple of options for maximum job history that we need to consider modifying.

The first option is “Maximum job history log size (in rows)” and the second option is “Maximum job history rows per job.” If you have any jobs that run every minute or every few minutes, that alone is going to take up a large percentage of the job history.

Lost to the Past

How much job history should you keep? I can confidently say the answer is “it depends.” How many jobs are configured and how often do they run? How many job steps are configured in those jobs? There’s not a standard set of numbers that works for everybody.

SQL Server gives you a baseline with the 1000 rows size and 100 rows per job but it’s up to you to figure what works for you. Adding a 0 to the end of those numbers so that you’re saving 10000 rows and 1000 rows per job might be a good starting point. I would prefer to estimate high and adjust from there.

Thanks for reading!