It is important to keep your Orchestrator database free from clutter. To do this, we recommend:

• Using SQL Server Maintenance Solution
• Creating a separate database to save data before deleting it
• Deleting old data periodically
• Backing up the database

Using SQL Server Maintenance Solution

SQL Server Maintenance Solution is a set of scripts that enable you to run backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server, starting with the 2005 version. Please see this GitHub project for more information.

Creating an Archive Database

It is recommended that you create a separate database in which to save items before you delete them. As a result, this database acts as an archive for the items that you may need to store for certain reasons, such as audits.

1. Create a new database called, for example, UiPathArchive.
2. Create the following backup tables:
   2.1. ArchiveLogs with the same structure as the Logs table:

select * into [UiPathArchive].[dbo].[ArchiveLogs] from [UiPath].[dbo].[Logs] where 1=2

2.2. ArchiveQueueItems with the same structure as the QueueItems table

select * into [UiPathArchive].[dbo].[ArchiveQueueItems] from [UiPath].[dbo].[QueueItems] where 1=2

3. Copy all the data from Logs and QueueItems into the corresponding archive tables before deleting anything.

Deleting Old Data Periodically

🚧

Important!

• When deleting old data, do not reset counts on ID columns (Primary Keys).

• Before running the following scripts, make sure to adapt them to your environment.

Queue Items

To delete successfully processed queue items (status = 3) older than 45 days, for example, use the query below. Optionally, you can include the TenantId and ReviewStatus.

It can be executed manually or scheduled in an SQL Server Job.

DELETE FROM [UiPath].[dbo].[QueueItems]
  /*
    0 = new, 1 = in progress, 2 = failed,
3 = success, 4 = invalid, 5 = retried
  */
  where status = 3
  --and ReviewStatus != 0
  --and TenantId = 1 -- default tenant
  and  DateDiff(day, CreationTime, GetDate()) > 45

📘

Note:

If you want to modify how old the items to be deleted should be, replace the number 45 with the desired number of days, on the last line.

Logged Messages

We recommend deleting log messages that are older than 45 days. The example query below deletes old messages with level Info, older than 45 days. Optionally, include the TenantId, or comment out the line and level = 2 to delete all the logged messages, regardless of their level.

It can be executed manually or scheduled in an SQL Server Job.

DELETE FROM [UiPath].[dbo].[Logs]
  /*
0 = Verbose, 1 = Trace, 2 = Info,
3 = Warn, 4 = Error, 5 = Fatal
*/
where 1=1
and level = 2
-- and TenantId = 1 -- default tenant
and  DateDiff(day, TimeStamp, GetDate()) > 45

📘

Note:

If you want to modify how old the items to be deleted should be, replace the number 45 with the desired number of days, on the last line.

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.

Backing Up the Database

We recommend you to implement regular backups of the SQL Server database, such as full weekly backups or daily incremental backups.

Additionally, we recommend you to use the DatabaseBackup stored procedure that is created using the script at this location.