Best practices for SQL Server Agent job history retention

Category: Reliability

Item: SQL Agent Jobs history settings

 

What is the SQL Server Agent Job History Retention?

It will determine the amount of Job History records that SQL Server before delete.

Agent job history set up to purge all history records once the history log reaches a certain number of rows by default.

Logs are helpful for troubleshooting; having insufficient records makes it more difficult to identify problems.

How to identify the issue and how to fix it?

  1. Connect to the SQL instance using SSMS (SQL Server Management Studio);
  2. Right-click on the SQL Server Agent and open the properties;
Figure 1- SQL Server Agent properties.
Figure 2- SQL Server Agent history size.

 

We recommend changing the Maximum job history log size (in rows) to 50,000 and Maximum job history rows per job to 1,000.

Note: These numbers can vary according to you with your business needs.

More information

Microsoft – Set Up the Job History Log

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.