Data Cleanup
When customer is interested in reducing of the DB space, he is able to configure data cleanup.
Warning! 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]<span style="color: #808080">.</span>[_Setting_DataCleanup]
Be default the values of the table are:
DataCategory | XmlDayToKeep | EtlDayToKeep | ReportingMonthToKeep | ReportingDateToKeep |
REPORT | 60 | 90 | NULL | NULL |
ASTATE | 5 | 90 | 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
Script to set default values:
MERGE INTO [Reporting].[_Setting_DataCleanup] AS Target
USING (VALUES ('REPORT', 60, 90, NULL, NULL)
, ('ASTATE', 5, 90, NULL, NULL)) AS Source ([DataCategory], [XmlDayToKeep], [EtlDayToKeep], [ReportingMonthToKeep], [ReportingDateToKeep])
ON Target.[DataCategory] = Source.[DataCategory]
WHEN MATCHED THEN UPDATE SET
[XmlDayToKeep] = Source.[XmlDayToKeep]
, [EtlDayToKeep] = Source.[EtlDayToKeep]
, [ReportingMonthToKeep] = Source.[ReportingMonthToKeep]
, [ReportingDateToKeep] = Source.[ReportingDateToKeep];
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'
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 NULLTenantId
TenantName
ReportingMonthToKeep
ReportingDateToKeep
2A45A4C2-C072-4376-A126-BDC5E07A055B
NULL
NULL
NULL
TenantId is inserted in the [Reporting]<span style="color: #808080">.</span>[_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'
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
This settings will force cleanup of all historical data from [etl] and [reporting] tables.
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 switch database recovery mode to SIMPLE before executing data cleanup.
Do not forget to set back to default values after cleanup is comlete!
Executing Data Cleanup
By default DataCleanup is included into integration job [Reporting].[usp_DataLoader] stored procedure. And 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'
Manual execution of Data Cleanup
If Data Cleanup is excluded from data loader 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
This script execute cleanup procedures regardless table [Reporting].[_DataLoaderCustomSteps] settings.