# Insights database maintenance

> Insights Database works using two tables: `dbo` and `read`. Both tables store data from Orchestrator databases and pass data between each other. Over time, tables store a large amount of data that can impact database performance. You can free up space by removing data that is not relevant or outdated.

## Overview

Insights Database works using two tables: `dbo` and `read`. Both tables store data from Orchestrator databases and pass data between each other. Over time, tables store a large amount of data that can impact database performance. You can free up space by removing data that is not relevant or outdated.

The estimated threshold for storing historical data is set to

* Up to 100 million jobs
* Up to 1 billion job events
* Up to 100 million queue items
* Up to 500 million queue item events
* Up to 1 billion robot logs

Use the following database maintenance SQL scripts to create SQL Stored Procedures, then execute these SQL Stored Procedures to either delete all data from `[dbo].Jobs`, `[dbo].JobEvents`, `[dbo].QueueItems`, `[dbo].QueueItemEvents`, `[dbo].RobotLogs` tables, or just the `[dbo].RobotLogs`.

:::note
Back up your database before executing the scripts. Before running the `PROCEDURE [read].[Delete_Insights_Data_Read]` and `PROCEDURE [read].[Delete_Process_Logs_Read]` scripts, the Insights module needs to be disabled.
:::

## Considerations

* Scripts with the `_DBO` suffix are used to delete data from DBO schema tables,
* Scripts with the `_Read` suffix are used to delete data from Read schema tables.
* Large-sized batches might impact the speed of execution. Consider using the default batch size, which is set to 100000.

## Prerequisites

Before running the `PROCEDURE [read].[Delete_Insights_Data_Read]` and `PROCEDURE [read].[Delete_Process_Logs_Read]` scripts, the Insights module needs to be disabled.

1. Log in to the VM where **Orchestrator** is installed.
2. Find the following: `C:\Program Files (x86)\UiPath\Orchestrator\UiPath.Orchestrator.dll.config``Insights.ModuleEnabled`.
3. Open the `UiPath.Orchestrator.dll.config` file using Notepad.
4. Find the following configuration value:
   ```
     "codes": [
       {
         "code": "<add key=\"Insights.ModuleEnabled\" value=\"true\" />",
         "language": "text",
         "name": "configuration value"
       }
     ]
   }
   ```
5. Replace `true` with `false`.
6. Save the file.
7. Go back to the VM where **Orchestrator** is installed and restart the VM.
8. Run the maintenance scripts.

:::note
Make sure to enable the Insights module back after running the maintenance scripts.
:::

## Resources

[SQL Stored Procedures download location](https://github.com/UiPath/Insights-Customer/tree/master/Scripts/SQLServer/DataTrimmingScripts)

## SQL stored procedure 1: delete all data based on cut-off timestamp

Generate and save the following SQL Stored Procedures to delete all data types (`Jobs`, `JobEvents`, `QueueItems`, `QueueItemEvents`, `RobotLogs`) for a cut-off timestamp.

 <colgroup>
  <col/>
  <col/>
  <col/>
 </colgroup>
 
  
    SQL Stored Procedures 
    Parameter 
    Description 
  
 
 
  
    Delete_Insights_Data_DBO 
   
      
         @CutoffTimeStamp <code>DATETIME</code> (Required) Delete all five types of data before a specified timestamp (e.g., <code>2021-01-01 01:00:05</code> ).  Do not provide a timestamp that deletes all data to an empty table.  
         @BatchSize <code>INT = 100000</code> (Optional) Default is set to <code>100000</code> .  Data is deleted batch by batch. For example, if you have 1 million RobotLogs and leave the default value, the delete command runs ten times to remove all data.  
      

    Delete data from <code>[dbo].[Jobs]</code> , <code>[dbo].[JobEvents]</code> , <code>[dbo].[QueueItems]</code> , <code>[dbo].[QueueItemEvents]</code> , <code>[dbo].[RobotLogs]</code> tables based on a cut-off timestamp. 
  
  
    Delete_Insights_Data_Read 
    N/A 
    Truncate <code>[read].Jobs</code> , <code>[read].JobEvents</code> , <code>[read].QueueItems</code> , <code>[read].QueueItemEvents</code> , <code>[read].RobotLogs</code> tables. 
  
 

:::note
The Insights Dashboard widgets are available again after SQL Stored Procedure 1 has finished, and the data has been backfilled. Keep in mind that the backfill process may take hours to complete given that you have a large amount of data. When you first run the `Delete_Insights_Data_DBO` SQL Script, it creates a `QueueItems.IX_CreationTime` nonclustered index with the purpose of making future executions faster.
:::

## SQL stored procedure 2: delete RobotLogs data based on ProcessNames and Tenant ID

Generate and save this SQL Stored Procedure to delete RobotsLogs based on ProcessNames and Tenant ID.

 <colgroup>
  <col/>
  <col/>
  <col/>
 </colgroup>
 
  
    SQL Stored Procedures 
    Parameter 
    Description 
  
 
 
  
    Delete_Process_Logs_DBO 
   
      
         @ProcessName <code>NVARCHAR(128)</code> (Required) 
      
 The Orchestrator process name. 
      
         @TenantId <code>INT</code> (Required) 
      
 Tenant ID is required as two tenants might run processes with identical names. 
      
         @BatchSize <code>INT = 100000</code> (Optional) 
      
 Delete data in batches of 100000. See  Considerations  .  
    Delete data from <code>[dbo].[RobotLogs]</code> table by ProcessName and Tenant ID. 
  
  
    Delete_Process_Logs_Read 
   
      
         @ProcessName <code>NVARCHAR(128)</code> (Required) 
         @TenantId <code>INT</code> (Required) 
      

    Truncate <code>[read].RobotLogs</code> table. 
  
 

:::note
The Insights Dashboard widgets related to RobotLogs are available again after SQL Stored Procedure 2 has finished, and the data has been backfilled. Keep in mind that the backfill process may take hours to complete given that you have a large amount of data.
:::

## Related articles

[Stored Procedures (Database Engine)](https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/stored-procedures-database-engine?view=sql-server-ver15)