ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Update the SQL Server Agent Job History Retention

book

Article ID: 172060

calendar_today

Updated On:

Products

Information Centric Analytics Data Loss Prevention Core Package

Issue/Introduction

History for the SQL Server Agent Jobs are only showing a few days or last few runs of the SQL Server Jobs. Depending on server setup, there could be multiple jobs setup. Each job will be "fighting" for a part of the job history log. The SQL Agent Job History retention settings are for the SQL Server instance and not specific to any one job.

Cause

By default, SQL Server Agent Job History is setup to purge all SQL Agent History records once the history log reaches a certain number of rows.

Environment

SQL Server

Resolution

Disable the size limit specified by the SQL Server Agent Properties.

1) Open an instance of SQL Server Management Studio (SSMS)

2) Connect to the ICA database server

3) In Object Explorer, expand the database server

4) Right click on the SQL Server Agent and click on Properties

5) In the SQL Server Agent Properties window, select History

6) You have the following options:

     a. (not recommended) Limit the size of the job history log:

          i. Maximum job history log size (in rows): specifies how many rows are retained in the history log

          ii. Maximum job history rows per job: specifies how my rows are retained for each job

     b. (recommended) Remove Agent History, Older than: specifies a cap on how long the SQL Server Agent Job History is retained.

Note: Option A is not recommended for the following reasons:

- The log is for all SQL Agent jobs on that SQL instance and not just Risk Fabric / ICA jobs

- Limiting to a number of rows could remove prior job history records if the row count has been exceeded or limit how much is stored

- Doesn't provide us or the user with a good historical view of the jobs

Attachments