Cleaning up the Task Mining database

This page describes the necessary maintenance tasks for the Task Mining databases.

Overview

The default Task Mining database, named [AutomationSuite_Task_Mining], uses a single schema [tddiscovery] for data storage. Primarily, Task Mining stores data in the object store, reducing the need of frequent database maintenance. If required, you can use the Remove_Active_History.sql database maintenance script to periodically clean up the following database table:

[AutomationSuite_Task_Mining].[tddiscovery].[ActivityHistory]

Important:

Ensure to back up the Task Mining database [AutomationSuite_Task_Mining] before executing any scripts.

Prerequisites

The following prerequisites are assumed:

You have access to the [AutomationSuite_Task_Mining] database with the required permissions.

Remove_Active_History.sql script

CREATE PROCEDURE [tddiscovery].[Delete_TaskMining_ActivityHistory] 
   @CutoffTimeStamp datetime2, 
   @BatchSize INT = 10000 
AS
BEGIN
  
   DECLARE @total_deleted_items BIGINT = 0; 
   
   SET NOCOUNT ON;
   
   -- delete ActivityHistoryItems 
   DECLARE @Deleted_Rows_ActivityHistoryItems INT = @BatchSize; 
   WHILE(@Deleted_Rows_ActivityHistoryItems = @BatchSize)
   BEGIN
     DELETE TOP(@BatchSize) FROM [tddiscovery].[ActivityHistory] WHERE At <= @CutoffTimeStamp; 
     SET @Deleted_Rows_ActivityHistoryItems = @@ROWCOUNT; 
     SET @total_deleted_items = @Deleted_Rows_ActivityHistoryItems + @total_deleted_items;
   END;

   PRINT('The script executed successfully!');
   PRINT('Total deleted rows from [tddiscovery].[ActivityHistory]: ' + CONVERT(NVARCHAR, @total_deleted_items));

END; 
GOCREATE PROCEDURE [tddiscovery].[Delete_TaskMining_ActivityHistory] 
   @CutoffTimeStamp datetime2, 
   @BatchSize INT = 10000 
AS
BEGIN
  
   DECLARE @total_deleted_items BIGINT = 0; 
   
   SET NOCOUNT ON;
   
   -- delete ActivityHistoryItems 
   DECLARE @Deleted_Rows_ActivityHistoryItems INT = @BatchSize; 
   WHILE(@Deleted_Rows_ActivityHistoryItems = @BatchSize)
   BEGIN
     DELETE TOP(@BatchSize) FROM [tddiscovery].[ActivityHistory] WHERE At <= @CutoffTimeStamp; 
     SET @Deleted_Rows_ActivityHistoryItems = @@ROWCOUNT; 
     SET @total_deleted_items = @Deleted_Rows_ActivityHistoryItems + @total_deleted_items;
   END;

   PRINT('The script executed successfully!');
   PRINT('Total deleted rows from [tddiscovery].[ActivityHistory]: ' + CONVERT(NVARCHAR, @total_deleted_items));

END; 
GO

Steps

Run the Remove_Active_History.sql database maintenance script to create the Delete_TaskMining_ActivityHistory Stored Procedure.

Note:
Always use the most recent version of the Stored Procedure.

If it already exists, drop the existing Stored Procedure and recreate it using the provided script. Alternatively, you can change CREATE to ALTER and then execute the script to update the stored procedure.
Execute the Delete_TaskMining_ActivityHistory Stored Procedure. This deletes all data related to Activity History for a cut-off timestamp from the [AutomationSuite_Task_Mining].[tddiscovery].[ActivityHistory] table.

SQL Stored Procedure: Delete active history

Attention:

Before executing the PROCEDURE [tddiscovery].[Delete_TaskMining_ActivityHistory] script, ensure that Task Mining usage is suspended.

The following table describes the parameters for the Delete_TaskMining_ActivityHistory SQL Stored Procedure.

Parameter	Description	Mandatory Y/N
`@CutoffTimeStamp`	Delete all data before a specified timestamp (e.g., 2021-01-01 01:00:05). Note: Do not provide a timestamp that leads to an empty table.	Y
`@BatchSize INT = 10000`	The number of rows to be deleted at a time. This enables you to delete the data batch by batch. `The default is set to 10000.`	N

The following code provides an example of how to run the Stored Procedure.

DECLARE @ProcessTime datetime2;
SET @ProcessTime = '2024-07-11 13:44:42.7856026';
EXEC [tddiscovery].[Delete_TaskMining_ActivityHistory] @CutoffTimeStamp = @ProcessTime;DECLARE @ProcessTime datetime2;
SET @ProcessTime = '2024-07-11 13:44:42.7856026';
EXEC [tddiscovery].[Delete_TaskMining_ActivityHistory] @CutoffTimeStamp = @ProcessTime;