Atria Job Runner - Database Maintenance

Atria Job Runner - Database Maintenance

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

 

No.

Code

Log Message

Log Level

1

DM-00

Initialize Database Maintenance Configuration

Verbose

2

DM-01

Started Running Database Maintenance Scripts

Verbose

3

DM-02

Finished Running Database Maintenance Scripts

Verbose

4

DM-03

Error While Running Database Maintenance Database. The error occurred is: {error message}

Error

5

DM-04

Retrieve Database Maintenance Parameters

Verbose

6

DM-11

Call Trim Azure Sync Job Script

Verbose

7

DM-12

Error While Trimming Azure Sync Job. The error occurred is: {error message}

Error

8

DM-13

Finished Running Trim Azure Sync Job Script

Verbose

9

DM-21

Call Trim Logs Script

Verbose

10

DM-22

Error While Trimming Logs Script. The error occurred is: {error message}

Error

11

DM-23

Finished Running Trim Logs Script. {number of rows} Rows are affected

Verbose

12

DM-31

Call Trim Request Logs Script

Verbose

13

DM-32

Error While Trimming Request Logs Script. The error occurred is: {error message}

Error

14

DM-33

Finished Running Trim Request Logs Script

Verbose

15

DM-41

Call Shrink Database Script

Verbose

16

DM-42

Error While Running Shrink Database Script. The error occurred is: {error message}

Error

17

DM-43

Finished Running Shrink Database Script

Verbose

 

References

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


    • Related Articles

    • Setup a Maintenance Mode Page in Atria

      Overview This article shows how to setup a Maintenance Mode Page in Atria during an upgrade or maintenance schedule Procedure Step 1 - Create an app_offline.htm file Create an app_offline.htm file that will be used as a page during the maintenance ...
    • PowerShell based batch processing with the Atria Job Runner

      Scheduling PowerShell Jobs Applies to Atria 15.2 or later Purpose PowerShell is a popular and powerful scripting language that is well known by Microsoft administrators. Atria has the ability to schedule PowerShell jobs that can be centrally managed ...
    • Job Management (Batch Processes)

      Applies to Atria 15 and later Introduction Atria Job management is designed to setup, execute and schedule batch processes. The initial implementation uses this feature to execute PSA integration processes (ConnectWise, Kaseya, AutoTask), you can ...
    • Atria Billing Setup User Guide

      Objective This article describes how to configure Atria to utilize the latest billing features. This document outlines the billing setup attributes that should be configured.    Applies to Introduced in Atria version 12.0.0 Billing Setup Overview To ...
    • Display Patterns for Billing Line Descriptions

      Purpose: When generating billing data, two descriptions are generated for each billable item - Line description (detailed) and summary descriptions.   The Summary Description serves two purpose: Used as a template for your invoice lines and reporting ...