Introduction

Database Maintenance Jobservice Extension runs a set of scripts to maintain logs, requests, requestlogs and azuresyncjob tables in Atria.

High Level Process

Once we run database maintenance Jobservice the process follows the below steps:

1: Retrieve parameters defined for the extension.

2: Trim Azure Sync Job Table.

3: Trim Logs Table.

4: Trim RequestLogs Table.

5: Shrink Database.

Configuration

At the moment we have provided the following configurations for our customers.

{
"RunTrimAzureSyncJobs": true,
"AzureSyncJobRetentionDays": 5,
"RunTrimRequestLogs":true,
"RunTrimLogs":true,
"RunShrinkDatabase":false,
"LogsRetentionDays":5,
"RequestLogsRetentionDays":5,
"BatchSize":15000,
"TimeOutInMinutes":30
  }

RunTrimAzureSyncJobs

In order to let clients decide if trimming azure sync jobs table is necessary to run or not. The default value is set to false.

RunTrimLogs

With this flag we can decide if we want to run scripts for trimming logs table or not. The default value is set to true.

RunTrimRequestLogs

With this flag we can decide if we want to trim requestlogs table or not. The default value is set to true.

RunShrinkDatabase

Using this flag clients have the option to choose if they want to shrink the database after tables are trimmed or not. The default value is false.

StopOnError

By default, when we run trim tables and an error occurs, we log the error and we start to trim next table. However, with this flag we can decide if we want to stop the process if any error occurs or not. The default value is false.

AzureSyncJobRetentionDays

Number of days we can inform job service to keep information in Azure Sync Job table. The default value is to 30.

LogsRetentionDays

Number of days we can inform job service to keep information in logs table. The default value is to 30.

RequestLogsRetentionDays

Number of days we can inform job service to keep information in Requestlogs table. The default value is to 30.

Truncate

When Truncate is set to true we truncate the database based on the value defined in ShrinkTargetPercent, frees up the space in database and returns it back to operating system, and when it is set to false, we shrink the database with NoTruncate argument.

ShrinkTargetPercent

This defines the target percent for shrink database when the value of Truncate is set to true.

BatchSize

As logs table might have a large number of records to be deleted, it might escalate the locking on logs table which prevents other users access to the table, and it will cause huge other problems as well. To prevent such issues, we have used a batching process to delete records. BatchSize defines the number of records we wish to be deleted in each batch when we run the process.

TimeOutInMinutes

When sending a request from Atria to the database, Entity Framework handles the request and waits for the task to finish the job. On a database with larger amount of data, we may need to increase the timeout for EF commands. This property gives you the option to customize the time for your requests. The default time is 20 minutes. It is suggested that if you run the Maintenance database for the first time increase the time to a greater value.

Stored Procedures

At this point, we have provided the following stored procedures to maintain the database:

• dbo.sp_MaintenanceTrimAzureSyncJobs

• dbo.sp_MaintenanceTrimLogs

• dbo.sp_MaintenanceTrimRequestLogs

• dbo.sp_MaintenanceShrinkDatabase

• dbo.sp_TrimAzureSyncJobs

Install Extension

By default, Database maintenance extension is not imported in the Job extensions and it has to be imported via powershell commands. To do this please follow the below steps in your Provisioning server:

1: Run PowerShell as Administrator and run command:

Import-AtriaExtension -Extension DatabaseMaintenance

2: Inside Jobs List page add a new Job and enter the information. Here is a sample screenshot:

3: Once the information is saved, you can run the job service.

Log Messages

References

https://www.ibm.com/docs/en/i2-ibase/9.0.1?topic=databases-batch-delete