Database Maintenance Extension

Overview

The Database Maintenance Extension runs a set of scripts to maintain the logs, requests, requestlogs, and azuresyncjob tables, as well as perform Index Maintenance in Atria. This can be used to increase performance and clear up space in your atria database.

The Database Maintenance Extension is configured as a scheduled job that runs in the Atria platform. This guide will show you how to setup and configure the Database Maintenance Job within Atria.

High-Level Process

When the Database Maintenance Job is executed, it follows these 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

6: Perform Index Maintenance

Installation

1. Navigate to your Provisioning Server, and run the following command in an administrative PowerShell session to determine whether the Database Maintenance is installed:

Get-AtriaComponent -Name DatabaseMaintenance

2. If the component is not already installed, run the following command to install it:

Import-AtriaExtension -Extension DatabaseMaintenance

3. Once this has been installed, navigate to Configuration > Jobs and click + Add.

4. Fill out the New Job Form with the following parameters:

• Label: Database Maintenance System
• Task: Database Maintenance
• Execution: Scheduled
• Log Level: Verbose (Enabled)
• CRON Expression:

0 0 * * 0

• Configuration:

{
    "RunTrimAzureSyncJobs": false,
    "RunTrimLogs": false,
    "RunTrimRequestLogs": false,
        "RunShrinkDatabase": false,
        "RunIndexMaintenance": false,
        "StopOnError": false,
    "AzureSyncJobRetentionDays": 30,
    "LogsRetentionDays": 30,
    "RequestLogsRetentionDays": 30,
    "Truncate": false,
    "ShrinkTargetPercent": 10,
    "BatchSize": 5000,
    "TimeOutInMinutes": 20,
    "IndexMaintenanceProperties": {
        "Databases": "OLM",
         "FragmentationLow": null,
        "FragmentationMedium": "INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE",
         "FragmentationHigh": "INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE",
         "FragmentationLevel1": 5,
        "FragmentationLevel2": 30,
        "MinNumberOfPages": 500,
         "FillFactor": 100,
        "PadIndex": null,
        "UpdateStatistics": null,
        "OnlyModifiedStatistics": "N",
        "TimeOutInMinutes": 10
    }
}

5. Click Save and run the job using the 3 dots on the right to test it.

info

This is a default configuration for the Database Maintenance job. The job has been set to run with none of the features enabled every Sunday at 00:00. To change the scheduling of the job, use the CRON expression. You can use the guide found here to learn more about CRON expressions within Atria. Continue with the guide to find out the how each parameter/ feature works or use our recommended configuration below.

Configuration

Our recommended configuration is shown in the following JSON block below. Tasks can be enabled or disabled by setting the value to true/false:

{
    "RunTrimAzureSyncJobs": true,
    "RunTrimLogs": true,
    "RunTrimRequestLogs": true,
        "RunShrinkDatabase": true,
        "RunIndexMaintenance": true,
        "StopOnError": true,
    "AzureSyncJobRetentionDays": 30,
    "LogsRetentionDays": 30,
    "RequestLogsRetentionDays": 30,
    "Truncate": true,
    "ShrinkTargetPercent": 10,
    "BatchSize": 5000,
    "TimeOutInMinutes": 20,
    "IndexMaintenanceProperties": {
        "Databases": "OLM",
         "FragmentationLow": null,
        "FragmentationMedium": "INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE",
         "FragmentationHigh": "INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE",
         "FragmentationLevel1": 5,
        "FragmentationLevel2": 30,
        "MinNumberOfPages": 500,
         "FillFactor": 100,
        "PadIndex": null,
        "UpdateStatistics": null,
        "OnlyModifiedStatistics": "N",
        "TimeOutInMinutes": 10
    }
}

Configuration Flags

RunTrimAzureSyncJobs

• Description: Lets clients decide if trimming the Azure sync jobs table is necessary.
• Default Value: false

RunTrimLogs

• Description: Allows deciding whether to run scripts for trimming the logs table.
• Default Value: false

RunTrimRequestLogs

• Description: Allows deciding whether to trim the request logs table.
• Default Value: false

RunShrinkDatabase

• Description: Gives clients the option to shrink the database after tables are trimmed.
• Default Value: false

RunIndexMaintenance

• Description: Resolves fragmentation issues and updates statistics for all tables in the database.
• Default Value: false

StopOnError

• Description: Logs errors and continues with the next job by default. Use this flag to stop the process if an error occurs.
• Default Value: false

Retention Days Settings

AzureSyncJobRetentionDays

• Description: Number of days to retain information in the Azure Sync Job table.
• Default Value: 30

LogsRetentionDays

• Description: Number of days to retain information in the logs table.
• Default Value: 30

RequestLogsRetentionDays

• Description: Number of days to retain information in the request logs table.
• Default Value: 30

Shrink and Truncate Settings

Truncate

• Description: When true, truncates the database based on ShrinkTargetPercent. Frees up space in the database and returns it to the operating system. When false, shrinks the database with the NoTruncate argument.

ShrinkTargetPercent

• Description: Defines the target percentage for shrinking the database when Truncate is set to true.

Batch Processing Settings

BatchSize

• Description: Defines the number of records to delete in each batch when processing large tables.
  • As the logs table might have a large number of records to be deleted, it might escalate the locking on the logs table, which prevents other users from accessing the table, causing significant problems. To prevent such issues, a batching process is used to delete records.

TimeOutInMinutes

• Description: Sets the timeout for Entity Framework (EF) commands. Useful for databases with large amounts of data.

  • When sending a request from Atria to the database, Entity Framework handles the request and waits for the task to complete. On a database with a large amount of data, the timeout for EF commands may need to be increased. This property allows you to customize the time for your requests.
  • Suggestion: If you run the Maintenance database for the first time, it is recommended to increase the timeout to a greater value.

• Default Value: 20 minutes

Index Maintenance Specific Properties

Databases

• Description: Specifies the databases on which to perform index maintenance. This is a comma-separated list.
• Default Value: OLM
• Note: If you specify any database other than OLM, ensure OLMUser has permissions to modify objects in specified databases.

Fragmentation Levels

• FragmentationLow: Action for indexes with low fragmentation (less than FragmentationLevel1).
  • Default Value: null
• FragmentationMedium: Action for indexes with medium fragmentation (between FragmentationLevel1 and FragmentationLevel2).
  • Default Value: "INDEX_REORGANIZE, INDEX_REBUILD_ONLINE, INDEX_REBUILD_OFFLINE"
• FragmentationHigh: Action for indexes with high fragmentation (greater than FragmentationLevel2).
  • Default Value: "INDEX_REBUILD_ONLINE, INDEX_REBUILD_OFFLINE"

Fragmentation Thresholds

• FragmentationLevel1: Lower threshold for fragmentation.
  • Default Value: 5%
• FragmentationLevel2: Upper threshold for fragmentation.
  • Default Value: 30%

Additional Index Settings

• MinNumberOfPages: Minimum number of pages for index maintenance to be performed.
  • Default Value: 500
• FillFactor: Fill factor when rebuilding the index.
  • Default Value: 100
• PadIndex: Padding index to apply.
  • Default Value: null
• UpdateStatistics: Specifies how to update statistics on tables. Available values: ALL | TABLE | COLUMN | NULL.
  • Default Value: null
• OnlyModifiedStatistics: Updates only modified objects since the last statistics update.
  • Default Value: "N"
• TimeoutInMinutes: Timeout for performing updates.

Stored Procedures

The following stored procedures are provided to maintain the database:

• dbo.sp_MaintenanceTrimAzureSyncJobs
• dbo.sp_MaintenanceTrimLogs
• dbo.sp_MaintenanceTrimRequestLogs
• dbo.sp_MaintenanceShrinkDatabase
• dbo.sp_MaintenanceIndexOptimize

Log Messages

Code	Log Message	Log Level
DM-00	Initialize Database Maintenance Configuration	Verbose
DM-01	Started Running Database Maintenance Scripts	Verbose
DM-02	Finished Running Database Maintenance Scripts	Verbose
DM-03	Error While Running Database Maintenance Database. The error occurred is: {error message}	Error
DM-04	Retrieve Database Maintenance Parameters	Verbose
DM-11	Call Trim Azure Sync Job Script	Info
DM-12	Error While Trimming Azure Sync Job. The error occurred is: {error message}	Error
DM-13	Finished Running Trim Azure Sync Job Script	Info
DM-21	Call Trim Logs Script	Info
DM-22	Error While Trimming Logs Script. The error occurred is: {error message}	Error
DM-23	Finished Running Trim Logs Script. {number of rows} Rows are affected	Info
DM-31	Call Trim Request Logs Script	Info
DM-32	Error While Trimming Request Logs Script. The error occurred is: {error message}	Error
DM-33	Finished Running Trim Request Logs Script	Info
DM-41	Call Shrink Database Script	Info
DM-42	Error While Running Shrink Database Script. The error occurred is: {error message}	Error
DM-43	Finished Running Shrink Database Script	Info
DM-51	Call Index Optimize Script	Info
DM-52	Error While Optimizing Indexes. The error occurred is: {error message}	Error
DM-53	Finished Running the Index Optimization Script	Info

References

• IBM Documentation on Batch Delete