Skip to main content

Database Maintenance Extension

Overview​

The Database Maintenance extension runs selected maintenance operations against Atria's OLM and AtriaConfigService databases. It can remove retained operational records, maintain indexes and shrink databases. Configure it as a job so that maintenance runs at an appropriate time for your platform.

Only operations you enable run. All operation flags default to false, so importing the extension or saving configuration {} does not enable data cleanup.

The job processes enabled operations in this order:

  1. Trim Azure sync jobs, logs, request logs and job runs.
  2. Trim Config Service access logs and application registrations.
  3. Perform index maintenance.
  4. Shrink OLM and/or AtriaConfigService if enabled and a preceding relevant operation succeeded.
  5. Remove duplicate object-audit entries if enabled.

Installation​

On the Provisioning Server, open an administrative PowerShell session:

Get-AtriaComponent -Name DatabaseMaintenance
Import-AtriaExtension -Extension DatabaseMaintenance

See extension import parameters for version and restart options.

Import Atria Extension

Open Configuration > Provisioning & Debug Tools > Jobs. Check for an existing maintenance job before choosing + Add. For a new job:

  • Label: Database Maintenance
  • Task: Database Maintenance
  • Execution: Scheduled, or on demand for an initial test.
  • Log Level: Verbose while verifying the setup.
  • CRON Expression: For example, 0 0 * * 0 for Sunday at 00:00 UTC.
  • Configuration: Use the settings below.

Database Maintenance job configuration

Save the job, then use its actions menu to run it manually and inspect the run details.

Run the Database Maintenance job

Configuration​

Example: trim operational history​

This example enables retention-based cleanup in OLM and the Config Service, keeping index maintenance and shrinking disabled. Set retention periods to suit your reporting and support needs before running it; trimming deletes older records.

{
"RunTrimAzureSyncJobs": true,
"RunTrimLogs": true,
"RunTrimRequestLogs": true,
"RunTrimJobRuns": true,
"RunTrimConfigServiceAccessLogs": true,
"RunTrimConfigServiceAppRegistrations": true,
"RunIndexMaintenance": false,
"RunShrinkDatabase": false,
"RunTrimDuplicateObjectsAT": false,
"StopOnError": true,
"AzureSyncJobRetentionDays": 30,
"LogsRetentionDays": 30,
"RequestLogsRetentionDays": 30,
"JobRunsRetentionDays": 90,
"AccessLogsRetentionDays": 90,
"AppRegistrationsRetentionDays": 30,
"BatchSize": 5000,
"TimeOutInMinutes": 20
}

The flags and defaults below describe the extension implementation. Available options depend on the installed extension version.

Configuration flags​

PropertyDefaultOperation
RunTrimAzureSyncJobsfalseTrim retained Azure sync-job records.
RunTrimLogsfalseTrim retained log records.
RunTrimRequestLogsfalseTrim retained request logs.
RunTrimJobRunsfalseTrim retained job-run history.
RunTrimConfigServiceAccessLogsfalseTrim access logs in the Config Service database.
RunTrimConfigServiceAppRegistrationsfalseTrim Config Service application registrations and their scopes.
RunIndexMaintenancefalseRun index maintenance using IndexMaintenanceProperties.
RunShrinkDatabasefalseShrink a database after a successful relevant trimming or index operation.
RunTrimDuplicateObjectsATfalseRemove consecutive duplicate object-audit entries matching the procedure's comparison fields. This does not use a retention-days setting.
StopOnErrorfalseStop on an error in the trimming or index stages. Otherwise those stages log the error and continue. Shrink and duplicate-audit cleanup failures stop the job regardless of this flag.

Retention settings​

PropertyDefaultApplies to
AzureSyncJobRetentionDays30Azure sync-job records.
LogsRetentionDays30Log records.
RequestLogsRetentionDays30Request logs.
JobRunsRetentionDays90Job-run history.
AccessLogsRetentionDays90Config Service access logs.
AppRegistrationsRetentionDays30Config Service application registrations.

A retention setting takes effect only when its corresponding operation is enabled.

Batch and timeout settings​

PropertyDefaultBehaviour
BatchSize5000Records per deletion batch for the Azure sync-job and log trimming operations. Batching limits the size of each deletion.
TimeOutInMinutes20Command timeout used for trimming operations. Index maintenance has its own timeout below.

Large initial cleanups may need a longer timeout. Review the job-run messages to identify the stage that needs adjustment.

Shrink settings​

PropertyDefaultBehaviour
TruncatefalseFor OLM, selects whether shrinking can release free space to the operating system. With false, the procedure uses NOTRUNCATE. This is a shrink option, not a request to truncate tables.
ShrinkTargetPercent10Target percentage of free space for the OLM shrink when Truncate is true; also passed to the Config Service shrink.

RunShrinkDatabase alone does not trigger shrinking. OLM is eligible after a successful OLM trim or index operation targeting OLM; AtriaConfigService is eligible after a successful Config Service trim. Config Service shrinking uses ShrinkTargetPercent and does not receive the Truncate flag.

Index maintenance​

Set RunIndexMaintenance to true to use the following nested configuration. This example shows the defaults:

{
"RunIndexMaintenance": true,
"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
}
}
Property under IndexMaintenancePropertiesDefaultPurpose
DatabasesOLMComma-separated database list. If targeting other databases, ensure OLMUser has the required permissions there.
FragmentationLownullAction below the lower fragmentation threshold.
FragmentationMediumINDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINEActions for the middle fragmentation range.
FragmentationHighINDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINEActions for the high fragmentation range.
FragmentationLevel15Lower fragmentation threshold, as a percentage.
FragmentationLevel230Upper fragmentation threshold, as a percentage.
MinNumberOfPages500Minimum index size in pages.
FillFactor100Fill factor used when rebuilding indexes.
PadIndexnullIndex padding option.
UpdateStatisticsnullStatistics to update: ALL, TABLE, COLUMN, or null for no statistics update.
OnlyModifiedStatisticsNWhether statistics updates are limited to modified statistics.
TimeOutInMinutes10Index-maintenance command timeout.

Stored procedures​

The OLM maintenance operations use:

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

Config Service maintenance is requested through its maintenance client.

Check results​

Review the job run for each enabled stage and its errors or affected-row count. When StopOnError is false, an overall successful result can still contain errors from earlier stages. If you need the run to fail when a trimming or index stage fails, set StopOnError to true.

For execution, scheduling and log controls, see Jobs System and the CRON expression guide.

Log messages​

CodeLog MessageLog Level
DM-00Initialize Database Maintenance ConfigurationVerbose
DM-01Started Running Database Maintenance ScriptsVerbose
DM-02Finished Running Database Maintenance ScriptsVerbose
DM-03Error While Running Database Maintenance Database. The error occurred is: {error message}Error
DM-04Retrieve Database Maintenance ParametersVerbose
DM-11Call Trim Azure Sync Job ScriptInfo
DM-12Error While Trimming Azure Sync Job. The error occurred is: {error message}Error
DM-13Finished Running Trim Azure Sync Job ScriptInfo
DM-21Call Trim Logs ScriptInfo
DM-22Error While Trimming Logs Script. The error occurred is: {error message}Error
DM-23Finished Running Trim Logs Script. {number of rows} Rows are affectedInfo
DM-31Call Trim Request Logs ScriptInfo
DM-32Error While Trimming Request Logs Script. The error occurred is: {error message}Error
DM-33Finished Running Trim Request Logs ScriptInfo
DM-41Call Shrink Database ScriptInfo
DM-42Error While Running Shrink Database Script. The error occurred is: {error message}Error
DM-43Finished Running Shrink Database ScriptInfo
DM-51Call Index Optimize ScriptInfo
DM-52Error While Optimizing Indexes. The error occurred is: {error message}Error
DM-53Finished Running the Index Optimization ScriptInfo
DM-61Call Trim ConfigService Access Logs ScriptInfo
DM-62Error While Running Trim ConfigService Access Logs Script. The error occurred is: {error message}Error
DM-63Finished Running Trim ConfigService Access Logs ScriptInfo
DM-71Call Trim ConfigService App Registrations ScriptInfo
DM-72Error While Running Trim ConfigService App Registrations Script. The error occurred is: {error message}Error
DM-73Finished Running Trim ConfigService App Registrations ScriptInfo
DM-81Call Trim Job Runs ScriptInfo
DM-82Error While Running Trim Job Runs Script. The error occurred is: {error message}Error
DM-83Finished Running Trim Job Runs ScriptInfo
DM-91Call Trim ObjectsAT Entries ScriptInfo
DM-92Error While Trimming ObjectsAT Entries Script. The error occurred is: {error message}Error
DM-93Finished Running Trim ObjectsAT Entries ScriptInfo

References​