Database Maintenance Extension

Overview

The Database Maintenance Jobservice Extension runs a set of scripts to maintain the logs, requests, requestlogs, and azuresyncjob tables, as well as perform Index Maintenance in Atria.

High-Level Process

When the Database Maintenance Jobservice 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.

Configuration

The default configuration is shown in the following JSON block below. All tasks are disabled by default, so they need to be explicitly enabled for any of the tasks to run. Multiple tasks can be configured to run in the same batch by setting the values to true.

{
    "RunTrimAzureSyncJobs": false,
    "RunTrimLogs": false,
    "RunTrimRequestLogs": false,
        "RunShrinkDatabase": false,
        "RunIndexMaintenance": false,
        "StopOnError": false,
    "AzureSyncJobRetentionDays": 30,
    "LogsRetentionDays": 30,
    "RequestLogsRetentionDays": 30,
    "Truncate": true|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
    }
}

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

Install Extension

By default, the 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 steps below on your Provisioning server:

1. Run PowerShell as Administrator and run the following command:

   Import-AtriaExtension -Extension DatabaseMaintenance

2. Inside the 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

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

1. IBM Documentation on Batch Delete