SQL Agent Job History Log Size

Aim: To adjust the size of the SQL Agent job history log to retain more or less history as required.

On some SQL Servers with a large number of frequently run jobs (e.g. servers with multiple log-shipped databases), it is necessary to adjust the size of the job history log to ensure that enough history is retained to allow baselining & troubleshooting. If the default size of the log is too small, then relevant records may be lost before they can be investigated. On the other hand, if the history log size is too large, it can adversely impact the time taken for jobs to complete as the log becomes bloated.

Before adjusting the size of the job history log, it is useful to determine roughly how many rows should be retained in total & per job in order to have a useful history. To do this, I usually take note of how many jobs are running on the server, the frequency for each, & how many steps each job has (because the number of rows per execution will be the number of steps plus 1 for the job itself). Then I can calculate how many total & per-job rows are generated on an hourly basis & scale that up to number of hours or days I want to retain the history for.

To adjust the size of the history log, connect to the instance in SSMS with a login that has sysadmin permissions on the instance. In Object Explorer, right-click on the SQL Server Agent & click Properties. In the pop-up window, navigate to History. Adjust the numbers for “Maximum job history log size (in rows)” & “Maximum job history rows per job” according to your requirements. In the case of my server with multiple log-shipped databases, I have adjusted these to 20,000 rows & 1,000 rows.

After making the changes, monitor the number of hours or days of history retained & the times taken for each job on the server as the log grows to its maximum size. If insufficient history is retained, increase the number of rows retained incrementally & continue monitoring. If the job completion times become unacceptably long, reduce the number of rows retained until a suitable balance is found.

Next: In some cases, it is not possible to retain sufficient history log rows while at the same time maintaining fast job completion times so an alternative solution may be required. I’ll address this in a future blog post.