A maintenance task that many 'reluctant DBAs' commonly overlook is the need to keep historical information in the msdb database trimmed, or 'truncated' on a semi-regular basis. Otherwise, it IS possible for so much data to get 'crammed' into this database over time to the point where performance can actual degrade. A common example or scenario of this is when you keep too much backup history on tab and go to use SQL Server Management Studio (SSMS) to run a 'restore' operation and the dialog just seems to churn, and churn, and churn, and churn forever (as it's silently processing virtually every restore operation from the vast store of historical information it has on hand).) As such, I thought it would be worthwhile to point out a couple of key considerations and details associated with truncating SQL Server Job history.
Related: Semi-Advanced Logging Options for SQL Server Agent Jobs
How Much History Should You Keep On Hand?
Of course, it is possible to become a bit over-zealous when it comes to truncating historical data—something that I’ve encountered a few times. In fact, it's pretty easy for admins to go with the ‘default’ and only retain roughly 1000 of the latest entries for SQL Server Agent job outcomes. Problem is, if you've got a couple of actively-executed jobs (running, say, every 5-10 minutes) those will easily add up to roughly 150 or 300 entries (i.e., rows) per day. Lump a couple of over-active jobs like that together on a busy server, and it's possible that you won't even be able to go and check up and see how long backups from the night before were taking—because you're regularly pushing that info out of the 1000 row 'buffer' you've set up by default.
Personally, I typically like to keep around 3-6 weeks of data on hand for SQL Server Agent Job executions/outcomes. That's long enough that if I think I can spot a pattern (backups starting to take significantly longer, or irregularities with various jobs, etc.). I can then walk back through some of the historical details on hand to see if it looks like there's actually something going on or not. It's also NOT so much data that it begins to become problematic.
How to Truncate SQL Server Agent Job History
Clearing job history is something that could be done pretty easily from the SQL Server Management Studio (SSMS) GUI pretty easily. Er, well, that’d be the case if it weren't for two fairly weird problems with how the GUI is set up. To access the ‘weirdness’ in question, just right click on the SQL Server Agent node in SSMS for the server you want to configure, and select Properties—then switch to the History tab—where you'll see something similar to the following:
Which is where the problems begin – because while the top option/section of this dialog is pretty simple to understand (i.e., where you get to specify how many rows to keep), it’s also worth noting that it's fairly hard to turn things like row-counts into a 'temporal' value like a number of days or weeks. (Yeah, you could go in and query msdb..sysjobhistory, that's a bit of a pain.)
Here too, however, is also where the UI makes things a bit counter-intuitive as well—as the second option/check-box looks like you could simply do something like what I've done in the following screenshot and basically keep everything under 6 weeks in age:
The problem that clicking OK after you set something like that is that SQL Server will just go out and 'manually' (or one time only) remove any/all history greater than 6 weeks old. Or, stated differently, the UI may make it look like you can either choose to keep row counts OR (er, well, and/or) keep history based on age, but that's NOT actually what's going on. Instead, the top part of this tab lets you specify 'historical retention' in the sense that it’s a configuration setting that you specify and which will be 'honored' going forward; whereas the bottom part of this dialog gives you the option to just 'manually' remove any/all history greater than an expiration specified—but this is a 'one-off' operation and not something that will be 'honored' or treated as a config setting.
And, if you'd like to see that for yourself, just go in, make some changes to both/either set of options, and then click on the Script > Script Action To New Query Window (for example) and you’ll see exactly what SQL Server will or would be doing when you clicked OK. The good news, however, is that in so-doing, you can see that the 'Remove agent history Older than xxx' option just makes a static/single call into msdb.dbo.sp_purge_history—and passes in a date derived from whatever input you’ve specified.
As such, what I commonly do to keep history tame is the following:
- Create a new SQL Server Agent Job called Truncate History.
- Schedule it to run weekly (early Saturday or Sunday morning—typically).
- Add in a Job Step that truncates backup history (i.e., something that sets up a variable with the value of DATEADD(dd, GETDATE(), -NumberOfDaysInQuestion), and then passes that to a call to EXEC msdb.dbo.sp_delete_backuphistory @variableHere )
- Add in another job with msdb.dbo.sp_purge_history—with another/new loaded variable for x-days-back as well.
- Optionally cycle the SQL Server logs and/or SQL Server Agent Logs—depending upon how regularly the server gets rebooted (via msdb..sp_cycle_errorlog and msdb..sp_cycle_agent_errorlog).
- Optionally throw in any additional cleanup of replication, log shipping, or other history—as needed.
I find the steps above a lot easier than periodically going in and manually truncating history using the second part of the UI and using sp_purge_history with a cut-off date is much easier and logically more intuitive than trying to keep things sorted out by rowcounts.
