# Maintenance Considerations
> It is important to keep your Orchestrator database neat and tidy, to have a smooth experience during day to day operations, as well as during upgrades.
It is important to keep your Orchestrator database neat and tidy, to have a smooth experience during day to day operations, as well as during upgrades.
:::note
You do not need to upgrade or replace these scripts each time Orchestrator is upgraded. They will continue to function as expected across Orchestrator versions.
:::
To do this, we recommend that you use one of the scripts outlined below to regularly clean up old data, with the option to schedule their execution at times that are convenient for you, as long as your other database actions allow for it.
:::important
You can download the Orchestrator database maintenance scripts from the [Customer Portal](https://customerportal.uipath.com/product-downloads?q=Database%20Maintenance%20Scripts&s=recently_created&d=1).
:::
## Backing Up the Database
Before you start, we recommend implementing regular backups of the SQL Server database, such as full weekly or daily incremental backups.
Additionally, we recommend using the DatabaseBackup stored procedure that is created using the script at [this location](https://ola.hallengren.com/sql-server-backup.html).
## Database cleanup
The following scripts replace any and all previous database cleanup scripts. They cover all necessary maintenance tasks and can be configured as needed.
:::important
* You can download the Orchestrator
database maintenance scripts from the [Customer Portal](https://customerportal.uipath.com/product-downloads?q=Database%20Maintenance%20Scripts&s=recently_created&d=1).
:::
### SQL script
#### Prerequisites
This is what you need to be able to run the `CreateOrchestratorCleanupObjects.sql` script:
* You must have access to the archive database from within the context of your Orchestrator database.
* You must be able to access the archive using 3-part names, such as `Archive.dbo.QueueItemsTableArchive`.
#### How to use it
1. Run the `CreateOrchestratorCleanupObjects.sql` SQL script to create the following objects:
* The `dbo.__CleanupLog` table, which houses the execution logs.
* The `dbo.GetOrCreateArchiveTable` procedure, which creates or returns the archive table.
* The `dbo.RunOrchestratorCleanup` procedure, which performs the deletion and, optionally, the archival of old data.
2. Execute `dbo.RunOrchestratorCleanup` with the scheduling XML configuration file to perform the cleanup. For details on the XML file, see the **Scheduling the cleanup** section.
:::note
You can also use [SQL Server Agent](https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-job) to perform the cleanup.
:::
#### Script sample
```
DECLARE @cleanupConfigXml XML =
'
';
EXEC dbo.RunOrchestratorCleanup
@cleanupConfigXml = @cleanupConfigXml,
@archiveDatabaseName = 'OrchestratorArchive';
```
### PowerShell script
This is appropriate for situations where you cannot use the SQL script.
:::note
The PowerShell script is compatible with Powershell 5.1 and can be used with [Azure Automation runbooks](https://learn.microsoft.com/en-us/azure/automation/automation-runbook-types).
:::
#### Prerequisites
The `RunOrchestratorCleanup.ps1` script must be able to communicate with both these [connection strings](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.connectionstring):
* `SourceConnectionString` - this is the Orchestrator database connection string.
* `DestinationConnectionString` - this is the archive database connection string. Note that the archive database must be created and setup in advance.
#### Objects it creates
* The `dbo.__CleanupLog` table, which houses the execution logs.
* The `dbo.__CleanupIds` table, which stores a temporary batch of IDs that need to be cleaned up.
#### Limitations
The script opens two connections: one to the Orchestrator database (via `SourceConnectionString`) and one to the archive database (via `DestinationConnectionString`). As such, no actual transaction is involved, since the data is copied from Orchestrator to the archive, after which it is deleted from Orchestrator.
This means that, if an exception is thrown anywhere between the two actions, causing the script to be re-executed, the same data might be copied again, thus leading to duplicates in the archive database.
However, since uniqueness is not enforced in the archive database, this does not lead to any issues.
#### Script sample
```
.\RunOrchestratorCleanup
-SourceConnectionString "Data Source=.;Initial Catalog=UiPath;User ID=sa;Password=******"
-DestinationConnectionString "Data Source=.;Initial Catalog=;User ID=sa;Password=******"
-CleanupConfigXml ''
```
### Scheduling the cleanup
You can choose what data to clean up from your database, when, and for how long the cleanup activity should be performed for each run, among others.
To do that, use the following XML file with either the SQL or the PowerShell script. **It is preconfigured with our recommended parameters**, but you can edit it if you want more advanced features. Please make sure you understand what each parameter does before you edit the script, as this can have serious consequences.
```
```
Even if one run only manages to partially clean up one or a few tables, subsequent runs will continue the cleanup, up until the script catches up with all the backlog. It is, therefore, important that you do not accumulate a larger backlog than the script is able to handle. If this happens, consider scheduling the script to run more frequently.
#### Cleanup XML parameters
Parameter
Explanation
Possible values
totalRunMaxMinutes
The maximum number of minutes that the script is allowed to execute for all tables during one run.
Must be higher than 1.
name
The name of the table containing the data that you want to delete.
Example: QueueItems
runMaxMinutes
The maximum number of minutes that the script is allowed to execute for a particular table during one run. Important: The amount of time you set here might sometimes run over by a couple of minutes. In addition to that, the totalRunMaxMinutes takes precedence, and will always enforced, even if you set this parameter to -1 .
-1 : the script will be executed for an unlimited number of minutes. 0 : the script will not execute for that particular table. >0 (i.e. a number chosen by you): the maximum number of minutes that the script will execute.
idColumn
The ID of the column which contains the data you want to delete.
Example: Id from the QueueItems table
dateTimeColumn
This differs depending on the data type. It is used in combination with daysOld .
Example: CreationTime for QueueItems
additionalFilter
Any valid SQL statement for a filter. This can be left empty.
Example: Status IN (2, 3, 4, 5, 6) . This is included in the XML file recommended by us.
daysOld
Allows you to keep a certain number of days' worth of data. This is used in combination with dateTimeColumn .
This must be set to a minimum of 2 . Example: setting this parameter to 5 keeps all data that is 5 days old.
batchSize
The number of table rows to be deleted in one iteration. Important: This is not used for tables where foreign keys are defined.
Example: setting this parameter to 50 for the QueueItems table deletes 50 items from that particular table.
forceCascade
Allows you to execute the script for tables where foreign keys are defined. Important: The sample already uses forceCascade for the tables where it is necessary. You therefore do not need to change it.
0 : do not cascade. 1 : cascade. For example, setting this option for the QueueItems table will process both the QueueItemEvents and the QueueItemComments tables.
shouldArchive
Allows you to choose whether you want to archive the data.
0 : do not archive. 1 : archive.
### SQL and PowerShell scripts compared
Feature
SQL script
PowerShell script
Cleanup XML
They both use the logic described in the Scheduling the cleanup section.
Execution log
They both create a dbo.__CleanupLogs table to store the logs of the execution. You can query the logs of an execution by using SELECT * FROM dbo.__CleanupLog WHERE ExecutionId = '<execution_id>' ORDER BY Id . You can check if an execution contained errors by using SELECT * FROM dbo.__CleanupLog WHERE ExecutionId = '<execution_id>' AND IsError = 1 . The ExecutionId parameter is generated with each execution of either of these scripts.
Archive table
The archive table does not contain any indexes, foreign keys, or identity columns. Timestamp type columns are not archived.
Archive table name
The names follow the same logic, and include a string based on the table schema.
Batch of IDs to be archived/deleted
Stored in a temporary table.
Stored in the dbo.__CleanupIds table.
Transaction
A single transaction is performed for every batch, during which the data is both archived and deleted. The batch size, i.e. the number of table rows to be processed, is defined in the XML file, through the batchSize parameter.
SqlBulkCopy is used to copy the data. There is no single transaction performed for archival and deletion.
## Elasticsearch
Orchestrator keeps one index for each tenant, for every month. Keeping old indices in Elasticsearch, even if they are not used in searches or reports, may affect its performance (memory consumption). As a result, it is recommended to delete old indices.