Performing database maintenance

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

• Using SQL Server Maintenance Solution
• 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. For details, see this GitHub project.

Backing up the database

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.

Deleting old data periodically

Shared Suite Capabilities

Create a separate database in which to save items before you delete them. This database acts as an archive for the items you may need to store for certain reasons, such as audits.

1. Create a new database called, for example, UiPathArchives:

   create database UiPathArchives

2. Create the following backup tables:
   1. ArchiveAuditEvent with the same structure as the AuditEvent table:

      SELECT * INTO [UiPathArchives].[dbo].[ArchiveAuditEvent] from [accountmanagementdb].[adt].[AuditEvent] where 1 = 2

3. Archive the data:
   1. Archive audit records

      DECLARE @NumberOfDaysToKeep INT
      DECLARE @CurrentDate DATETIME
      
      -- Specify the number of days
      SET @NumberOfDaysToKeep = 60
      -- Archive the list of audit event records that you want to delete
      SET @CurrentDate = GetDate()
      BEGIN TRANSACTION
      INSERT INTO [UiPathArchives].[dbo].[ArchiveAuditEvent]
      SELECT
      [Id],[CreatedOn],[Version],[OrganizationId],[Source],[Category],[Action],[IsUserEvent],
      [UserId],[FullName],[Email],[DetailsVersion],[Details],[OperationId]
      FROM [adt].[AuditEvent]
      WHERE DateDiff(day, CreatedOn, @CurrentDate) > @NumberOfDaysToKeep
      -- Delete the audit events
      DELETE FROM [adt].[AuditEvent]
      WHERE EXISTS (SELECT 1 FROM [UiPathArchives].[dbo].[ArchiveAuditEvent] WHERE Id = [adt].[AuditEvent].[Id])
      COMMIT TRANSACTION

      Old data is copied to these archives prior to being deleted when using the query below.

4. Delete data from the table.
   Important: Before running the following script, make sure to adapt them to your environment.
   1. Audit events

      declare @NumberOfDaysToKeep int
      declare @CurrentDate datetime
      
      -- Specify the number of days
      SET @NumberOfDaysToKeep = 60
      -- Create temporary table with the list of audit event records that you want to delete
      SET @CurrentDate = GetDate()
      SELECT
      [Id],[CreatedOn],[Version],[OrganizationId],[Source],[Category],[Action],[IsUserEvent],
      [UserId],[FullName],[Email],[DetailsVersion],[Details],[OperationId]
      INTO #TempAuditRecordsToDelete
      FROM [adt].[AuditEvent]
      WHERE DateDiff(day, CreatedOn, @CurrentDate) > @NumberOfDaysToKeep
      -- Review the audit event records to be deleted
      SELECT * FROM #TempAuditRecordsToDelete
      -- Delete the audit events
      BEGIN TRANSACTION
      DELETE FROM [adt].[AuditEvent]
      WHERE EXISTS (SELECT 1 FROM #TempAuditRecordsToDelete WHERE Id = [adt].[AuditEvent].[Id])
      DROP TABLE #TempAuditRecordsToDelete
      COMMIT TRANSACTION

Identity Server

Create a separate database in which to save items before you delete them. This database acts as an archive for the items you may need to store for certain reasons, such as audits.

1. Create a new database called, for example, UiPathIdentityArchives:

   create database UiPathIdentityArchives

2. Create the following backup tables:
   1. ArchiveLoginAttempts with the same structure as the UserLoginAttempts table:

      select * into [UiPathIdentityArchives].[dbo].[ArchiveUserLoginAttempts] from [UiPath].[dbo].[UserLoginAttempts] where 1=2

      Old data is copied to these archives prior to being deleted when using the query below.

3. Delete data from the table.
   Important: Before running the following script, make sure to adapt them to your environment.
   1. User login attempts
      To delete login attempts older than 60 days, for example, use the query below. It can be executed manually or scheduled in an SQL Server Job.

      declare @NumberOfDaysToKeep int
      set @NumberOfDaysToKeep = 60
      if OBJECT_ID('[UiPathIdentityArchives].[dbo].[UserLoginAttemps]') = NULL 
        begin select * into [UiPathIdentityArchives].[dbo].[UserLoginAttemps] from [identity].UserLoginAttempts where 1=2 end
      begin transaction
        set identity_insert [UiPathIdentityArchives].[dbo].[UserLoginAttemps] on
        insert into [UiPathIdentityArchives].[dbo].[UserLoginAttemps] ([Id],[PartitionId],[UserId],[UserNameOrEmailAddress],[ClientIpAddress],[ClientName],[BrowserInfo],[Result],[CreationTime],[AuthenticationProvider],[PartitionName])
          select [Id],[PartitionId],[UserId],[UserNameOrEmailAddress],[ClientIpAddress],[ClientName],[BrowserInfo],[Result],[CreationTime],[AuthenticationProvider],[PartitionName]
            from [identity].UserLoginAttempts where DateDiff(day, CreationTime, GetDate()) > @NumberOfDaysToKeep
        delete from [identity].UserLoginAttempts where DateDiff(day, CreationTime, GetDate()) > @NumberOfDaysToKeep
      commit transaction

Orchestrator

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.

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 below.

Note:

You can also use SQL Server Agent to perform the cleanup.

Script sample

DECLARE @cleanupConfigXml XML = 
'<CleanupConfig totalRunMaxMinutes="180">
	<Table name="QueueItems" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="CreationTime" additionalFilter="Status IN (2, 3, 4, 5, 6)" daysOld="180" batchSize="50" forceCascade="1" shouldArchive="1" />
</CleanupConfig>';

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.

Prerequisites

The RunOrchestratorCleanup.ps1 script must be able to communicate with both these connection strings:

• 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=<OrchestratorArchive>;User ID=sa;Password=******" 
-CleanupConfigXml '<CleanupConfig totalRunMaxMinutes="180"><Table name="QueueItems" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="CreationTime" additionalFilter="Status IN (2, 3, 4, 5, 6)" daysOld="180" batchSize="50" forceCascade="1" shouldArchive="1" /></CleanupConfig>'

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.

<CleanupConfig totalRunMaxMinutes="180">
    <Table name="QueueItems" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="CreationTime" additionalFilter="Status IN (2, 3, 4, 5, 6)" daysOld="180" batchSize="50" forceCascade="1" shouldArchive="1" />
    <Table name="Jobs" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="CreationTime" additionalFilter="State IN (4, 5, 6)" daysOld="180" batchSize="50" forceCascade="1" shouldArchive="1" />        
    <Table name="Logs" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="TimeStamp" additionalFilter="" daysOld="90" batchSize="1000" forceCascade="0" shouldArchive="1" />
    <Table name="AuditLogs" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="ExecutionTime" additionalFilter="" daysOld="365" batchSize="25" forceCascade="1" shouldArchive="1" />
    
    <Table name="Tasks" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="DeletionTime" additionalFilter="IsDeleted = 1" daysOld="180" batchSize="10" forceCascade="0" shouldArchive="1" />
    <Table name="QueueProcessingRecords" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="ProcessingTime" additionalFilter="ReportType != -1" daysOld="30" batchSize="1000" forceCascade="0" shouldArchive="0" />
    <Table name="Sessions" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="ReportingTime" additionalFilter="" daysOld="180" batchSize="50" forceCascade="1" shouldArchive="0" />    
    
    <Table name="RobotLicenseLogs" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="StartDate" additionalFilter="" daysOld="180" batchSize="1000" forceCascade="0" shouldArchive="0" />    
    <Table name="UserNotifications" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="CreationTime" additionalFilter="" daysOld="90" batchSize="1000" forceCascade="0" shouldArchive="0" />
    <Table name="TenantNotifications" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="CreationTime" additionalFilter="" daysOld="90" batchSize="1000" forceCascade="0" shouldArchive="0" />    
    <Table name="Ledger" runMaxMinutes="-1" idColumn="LedgerId" dateTimeColumn="CreationTime" additionalFilter="" daysOld="7" batchSize="1000" forceCascade="0" shouldArchive="0" />
    <Table name="LedgerDeliveries" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="LastUpdatedTime" additionalFilter="" daysOld="7" batchSize="1000" forceCascade="0" shouldArchive="0" />
    <Table name="__CleanupLog" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="ExecutionTimeUtc" additionalFilter="" daysOld="7" batchSize="1000" forceCascade="0" shouldArchive="0" />
</CleanupConfig>

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.