Increase SQL Server Agent job history logging

Products

• Information Centric Analytics

Issue/Introduction

The SQL Server Agent does not retain a complete history of Information Centric Analytics' (ICA) RiskFabric Processing job or the RiskFabric Intraday Processing job.

Environment

Version : 6.x

Component : Microsoft SQL Server

Cause

By default, the SQL Server Agent is configured to limit the size of the job history log. This limit is set on the number of rows to be retained (where each job step is equal to one row) and the job history log spans all SQL Server Agent jobs. As new rows are written, the oldest rows in the log are truncated. Because of this, a server hosting jobs for applications and services other than ICA will retain a shorter ICA job history than a server dedicated solely to ICA.

Resolution

To increase the SQL Server Agent job history log size or configure the log to be retained by date rather than size, follow this procedure:

1. Open SQL Server Management Studio (SSMS)
2. Connect to the Database Engine hosting the RiskFabric database
3. In Object Explorer, right-click the SQL Server Agent and select Properties
   The SQL Server Agent Properties window will open
4. Select the History page
5. Determine whether you wish to limit the job history by size, by date, or a combination of these settings:
   1. If by size, increase the Maximum job history log size (in rows) setting to your desired value
   2. If by date, uncheck the box labelled Limit size of job history log, check the box labelled Remove agent history, and set the date range to your desired value
6. Click the OK button to save changes and close the SQL Server Agent Properties window
7. In Object Explorer, right-click the SQL Server Agent and select Restart