When customer is interested in reducing of the DB space, he is able to configure data cleanup.

Be aware that the data is cleaned up forever and beyond recovery. So, data cleanup should be performed conscious.

Global Settings

Data cleanup is performed by data loader according to the configured settings in the table [Reporting].[_Setting_DataCleanup]

Be default the values of the table are:

DataCategory

XmlDayToKeep

EtlDayToKeep

ReportingMonthToKeep

ReportingDateToKeep

REPORT

60

360

NULL

NULL

ASTATE

5

360

NULL

NULL

ASTATE – tables that contain agent state data.

REPORT – tables that contain data about conversations and their transfers, forwards, recordings, modality, task completion information, etc.

XmlDayToKeep – cleans up XML data

EtlDayToKeep – cleans up ETL data

ReportingMonthToKeep and ReportingDateToKeep – cleans up reporting data

Rules of the reporting data cleaned up:

  • when XmlDayToKeep is configured, the data is cleaned up before (today’s day configured amount of days).
  • when EtlDayToKeep is configured, the data is cleaned up before (today’s day configured amount of days).
  • the default values of ReportingMonthToKeep and ReportingDateToKeep are NULL.
  • NULL for ReportingMonthToKeep and ReportingDateToKeep means that data is never cleaned up.
  • when ReportingDateToKeep is configured, the data is cleaned up before this date (the data of the configured date stays)
  • when ReportingMonthToKeep is configured, the data is cleaned up before (today’s month configured amount of months). E.g. when 0 is configured, the data before current month is cleaned up, only the data of current month stays.
  • when both ReportingMonthToKeep and ReportingDateToKeep are configured, they are compared and data is cleaned up before the most elder date.
  • ReportingDateToKeep configuration should be in format YYYY-MM-DD. ReportingDateToKeep setting is not inclusive, in order to cleanup reporting including date, you should specify next date.
  • ReportingMonthToKeep configuration should be a number.

Tenant cleanup Settings

Once you system has at least one Tenant you may enable per tenant cleanup.

Value with SetupKey CLEANUP_PER_TENANT in [Reporting].[_SysSetup] corresponds to enable -1 or disable – 0 tenant cleanup settings:

Example: Enable cleanup per tenant

UPDATE [Reporting].[_SysSetup]  SET SetupValue = 1 WHERE SetupKey = 'CLEANUP_PER_TENANT'
SQL
  • Tenant Cleanup settings cover only 2 values: ReportingMonthToKeep and ReportingDateToKeep. Xml and ETL data settings are global for the system.
  • It is important to remember that Tenant cleanup setting may be only reduce period of reporting data storage.
  • Tenant data cleanup is performed by data loader according to the configured settings in the table [Reporting].[_Setting_TenantDataCleanup]

Be default the TenantName, ReportingMonthToKeep and ReportingDateToKeep are NULL

TenantId

TenantName

ReportingMonthToKeep

ReportingDateToKeep

2A45A4C2-C072-4376-A126-BDC5E07A055B

NULL

NULL

NULL

TenantId is inserted in the [Reporting].[_Setting_TenantDataCleanup] on tenant creation.

In order to configure cleanup per tenant you should assign values to TenantName and one of the values fields ReportingMonthToKeep or ReportingDateToKeep.

Example:

UPDATE [Reporting].[_Setting_TenantDataCleanup] SET

TenantName = '01 SALES SERVICE',

ReportingDateToKeep = '2020-07-16'
SQL

(lightbulb) If TenantName is not configured or does not correspond to TenantId the setting is skipped.

Complete Data Cleanup

At some cases it is necessary delete all historical reporting from database. For this cleanup settings has to be adjusted as following:

  • XmlDayToKeep = -1
  • EtlDayToKeep = -1
  • ReportingMonthToKeep = -1
  • ReportingDateToKeep = NULL

SQL update script:

UPDATE dc SET

      XmlDayToKeep = -1

    , EtlDayToKeep = -1

    , ReportingMonthToKeep = -1

    , ReportingDateToKeep = NULL

FROM [Reporting].[_Setting_DataCleanup] dc
SQL

This settings will force cleanup of all historical data from [etl] and [reporting] tables.

(warning) Warning: On Large databases complete data cleanup may take significant time and lead to transaction log growth.
In order to increase cleanup performance and avoid significant transaction log growth we strongly recommend switching the database recovery mode to SIMPLE before executing data cleanup.

Executing Data Cleanup

By default DataCleanup is included into the integration job [Reporting].[usp_DataLoader] stored procedure. You do not need execute or schedule data cleanup manually.

Disabling Data Cleanup

For custom purposes you may need disable cleanup steps of the DataLoader and execute them manually. To achieve this update [IsEnabled] value in table [Reporting].[_DataLoaderCustomSteps] for the step you want to enable/disable.

StepId

RelatedStepId

Description

IsEnabled

32627338-DBCE-480B-B50D-40173616186B

NULL

XML_CLEANUP

1

C353ABF3-F9EB-4520-8D03-76A2645CE4E0

NULL

ETL_CLEANUP

1

3DFBFD3E-3A1C-448C-827E-B76927A49DEA

NULL

REPORTING_CLEANUP

1

For example: disable XML_CLEANUP step.

UPDATE [Reporting].[_DataLoaderCustomSteps]

SET [IsEnabled] = 0

WHERE [StepId] = '32627338-DBCE-480B-B50D-40173616186B'
SQL

Manual execution of Data Cleanup

If Data Cleanup is excluded from data loader, a manual cleanup execution may be needed. Use following code.

--XML_CLEANUP

EXEC [Reporting].[usp_Dataloader_Cleanup_XmlEvent] @IntegrationLogId = 1

--ETL_CLEANUP

EXEC [Reporting].[usp_DataLoader_Cleanup_EtlData] @IntegrationLogId = 1

--REPORTING_CLEANUP

EXEC [Reporting].[usp_DataLoader_Cleanup_Reporting] @IntegrationLogId = 1
SQL

(info) This script executes cleanup procedures regardless of table [Reporting].[_DataLoaderCustomSteps] settings.