Subscribe

UiPath Automation Suite

The UiPath Automation Suite Guide

Performing database maintenance

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

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.

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
  1. Create the following backup tables:
    2.1. ArchiveAuditEvent with the same structure as the AuditEvent table:
SELECT * INTO [UiPathArchives].[dbo].[ArchiveAuditEvent] from [accountmanagementdb].[adt].[AuditEvent] where 1 = 2
  1. Archive the data:
    3.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.

  1. Delete data from the table.

🚧

Important!

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

3.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
  1. Create the following backup tables:
    2.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.

  1. Delete data from the table.

🚧

Important!

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

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

Create a separate database in which to save items before you delete them. 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, UiPathOrchestratorArchives:
create database UiPathOrchestratorArchives
  1. Create the following backup tables:
    2.1. ArchiveLogs with the same structure as the Logs table:
select * into [UiPathOrchestratorArchives].[dbo].[ArchiveLogs] from [UiPath].[dbo].[Logs] where 1=2

2.2. ArchiveQueueItems, ArchiveQueueItemEvents, and ArchiveQueueItemComments with the same structure as the QueueItems, QueueItemsEvents , and QueueItemsComments tables:

select * into [UiPathOrchestratorArchives].[dbo].[ArchiveQueueItems] from
[UiPath].[dbo].[QueueItems] where 1=2
select * into [UiPathOrchestratorArchives].[dbo].[ArchiveQueueItemEvents] from [UiPath].[dbo].[QueueItemEvents] where 1=2
select * into [UiPathOrchestratorArchives].[dbo].[ArchiveQueueItemComments] from [UiPath].[dbo].[QueueItemComments] where 
1=2

2.3. ArchiveRobotLogs with the same structure as the RobotLogs table:

select * into [UiPathOrchestratorArchives].[dbo].[ArchiveRobotLicenseLogs] from [UiPath].[dbo].[RobotLicenseLogs] where 1=2

2.4. TenantNotificationsArchive and UserNotificationsArchive with the same structure as the TenantNotifications and UserNotifications tables:

select * into [UiPathOrchestratorArchives].[dbo].[ArchiveTenantNotifications] from
[UiPath].[dbo].[TenantNotifications] where 1=2
select * into [UiPathOrchestratorArchives].[dbo].[ArchiveUserNotifications] from [UiPath].[dbo].[UserNotifications] where 1=2

2.5. ArchiveJobs with the same structure as the Jobs table:

select * into [UiPathOrchestratorArchives].[dbo].[ArchiveJobs] from [UiPath].[dbo].[Jobs] where 1=2

2.5. ArchiveAuditLogs and ArchiveAuditLogEntities with the same structure as the AuditLog and AuditLogEntities tables:

select * into [UiPathOrchestratorArchives].[dbo].[ArchiveAuditLogs] from
[UiPath].[dbo].[AuditLogs] where 1=2
select * into [UiPathOrchestratorArchives].[dbo].[ArchiveAuditLogEntities] from [UiPath].[dbo].[AuditLogEntities] where 1=2

2.6 ArchiveTasks with the same structure as the Tasks table:

select * into [UiPathOrchestratorArchives].[dbo].[ArchiveTasks] from [UiPath].[dbo].[Tasks] where 1=2

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

  1. Delete data from the tables.

🚧

Important!

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

3.1. Queue Items

To delete successfully processed queue items, and related events and comments, of (status = 3) older than 60 days, for example, use the query below. Optionally, you can include a where clause for TenantId.

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

declare @NumberOfDaysToKeep int
set @NumberOfDaysToKeep = 60
begin transaction
-- create temp table with list of IDs that we want to delete
select ID as IdToDelete into #TempDeletedIds from QueueItems
where status=3
-- and TenantId = 1
-- and ReviewStatus != 0
and DateDiff(day, CreationTime, GetDate()) > @NumberOfDaysToKeep
-------------------- QueueItemEvents
set identity_insert [UiPathOrchestratorArchives].[dbo].[ArchiveQueueItemEvents] on
insert into [UiPathOrchestratorArchives].[dbo].[ArchiveQueueItemEvents]
([Id],[TenantId],[OrganizationUnitId],[QueueItemId],[TimeStamp],[IsDeleted],[DeleterUserId],[DeletionTime],[Action],[UserId],[Status],[ReviewStatus],[ReviewerUserId],[CreationTime],[CreatorUserId])
select
[Id],[TenantId],[OrganizationUnitId],[QueueItemId],[TimeStamp],[IsDeleted],[DeleterUserId],[DeletionTime],[Action],[UserId],[Status],[ReviewStatus],[ReviewerUserId],[CreationTime],[CreatorUserId]
from [UiPath].[dbo].[QueueItemEvents]
where Exists (select 1 from #TempDeletedIds where IdToDelete = QueueItemId)
delete from [UiPath].[dbo].[QueueItemEvents]
where Exists (select 1 from #TempDeletedIds where IdToDelete = QueueItemId)
set identity_insert [UiPathOrchestratorArchives].[dbo].[ArchiveQueueItemEvents] off
-------------------- QueueItemComments
set identity_insert [UiPathOrchestratorArchives].[dbo].[ArchiveQueueItemComments] on
insert into [UiPathOrchestratorArchives].[dbo].[ArchiveQueueItemComments]
([Id],[TenantId],[OrganizationUnitId],[QueueItemId],[Text],[IsDeleted],[DeleterUserId],[DeletionTime],[LastModificationTime],[LastModifierUserId],[CreationTime],[CreatorUserId])
select
[Id],[TenantId],[OrganizationUnitId],[QueueItemId],[Text],[IsDeleted],[DeleterUserId],[DeletionTime],[LastModificationTime],[LastModifierUserId],[CreationTime],[CreatorUserId]
from [UiPath].[dbo].[QueueItemComments]
where Exists (select 1 from #TempDeletedIds where IdToDelete = QueueItemId)
delete from [UiPath].[dbo].[QueueItemComments]
where Exists (select 1 from #TempDeletedIds where IdToDelete = QueueItemId)
set identity_insert [UiPathOrchestratorArchives].[dbo].[ArchiveQueueItemComments] off
-------------------- QueueItems
set identity_insert [UiPathOrchestratorArchives].[dbo].[ArchiveQueueItems] on
insert into [UiPathOrchestratorArchives].[dbo].[ArchiveQueueItems]
([Id],[Priority],[QueueDefinitionId],[Key],[Status],[IsDeleted],[DeleterUserId],[DeletionTime],[ReviewStatus],[RobotId],[StartProcessing],[EndProcessing],[SecondsInPreviousAttempts],[AncestorId],[RetryNumber],[SpecificData],[TenantId],[LastModificationTime],[LastModifierUserId],[CreationTime],[CreatorUserId],[DeferDate],[DueDate],[Progress],[Output],[OrganizationUnitId],[RowVersion],[ProcessingExceptionType],[HasDueDate],[Reference],[ReviewerUserId],[ProcessingExceptionReason],[ProcessingExceptionDetails],[ProcessingExceptionAssociatedImageFilePath],[ProcessingExceptionCreationTime],[CreatorJobId],[ExecutorJobId],[AnalyticsData],[RiskSlaDate]
)
select
[Id],[Priority],[QueueDefinitionId],[Key],[Status],[IsDeleted],[DeleterUserId],[DeletionTime],[ReviewStatus],[RobotId],[StartProcessing],[EndProcessing],[SecondsInPreviousAttempts],[AncestorId],[RetryNumber],[SpecificData],[TenantId],[LastModificationTime],[LastModifierUserId],
[CreationTime],[CreatorUserId],[DeferDate],[DueDate],[Progress],[Output],[OrganizationUnitId],NULL,[ProcessingExceptionType],[HasDueDate],[Reference],[ReviewerUserId],[ProcessingExceptionReason],[ProcessingExceptionDetails],[ProcessingExceptionAssociatedImageFilePath],[ProcessingExceptionCreationTime],[CreatorJobId],[ExecutorJobId],[AnalyticsData],[RiskSlaDate]
from [UiPath].[dbo].[QueueItems]
where Exists (select 1 from #TempDeletedIds where IdToDelete = [UiPath].[dbo].[QueueItems].[Id])
delete from [UiPath].[dbo].[QueueItems]
where Exists (select 1 from #TempDeletedIds where IdToDelete = [UiPath].[dbo].[QueueItems].[Id])
set identity_insert [UiPathOrchestratorArchives].[dbo].[ArchiveQueueItems] off
drop table #TempDeletedIds
commit transaction

3.2. Logged Messages

We recommend deleting log messages that are older than 60 days. The example query below deletes old messages with level Info, older than 60 days. Adapt the query to your needs, changing the @NumberOfDaysToKeep parameter or adding where clauses for Level and TenantId.

📘

Note:

If you don’t want to archive the logs that are deleted, simple remove the copy clause:
insert into [ArchiveLogs] select from [Logs].

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

declare @NumberOfDaysToKeep int
set @NumberOfDaysToKeep = 60
begin transaction
set identity_insert [UiPathOrchestratorArchives].[dbo].[ArchiveLogs] on
insert into [UiPathOrchestratorArchives].[dbo].[ArchiveLogs]
([Id],[OrganizationUnitId],[TenantId],[TimeStamp],[Level],[WindowsIdentity]
,[ProcessName],[JobKey],[RobotName],[Message],[RawMessage],[MachineId],[UserKey],[HostMachineName])
select
[Id],[OrganizationUnitId],[TenantId],[TimeStamp],[Level],[WindowsIdentity]
,[ProcessName],[JobKey],[RobotName],[Message],[RawMessage],[MachineId],[UserKey],[HostMachineName]
from [UiPath].[dbo].[Logs]
where 1=1
-- and level = 2
/*
  0 = Verbose, 1 = Trace, 2 = Info,
  3 = Warn, 4 = Error, 5 = Fatal
*/
-- and TenantId = 1 -- default tenant
and DateDiff(day, TimeStamp, GetDate()) > @NumberOfDaysToKeep
delete from [UiPath].[dbo].[Logs]
where 1=1
-- and level = 2
/*
  0 = Verbose, 1 = Trace, 2 = Info,
  3 = Warn, 4 = Error, 5 = Fatal
*/
-- and TenantId = 1 -- default tenant
and DateDiff(day, TimeStamp, GetDate()) > @NumberOfDaysToKeep
set identity_insert [UiPathOrchestratorArchives].[dbo].[ArchiveLogs] off
commit transaction

3.3. RobotLicense Logs

We recommend deleting the items that are older than 60 days in your RobotLicenseLogs table. The example query below deletes items older than 60 days. Adapt the query to your needs, changing the @NumberOfDaysToKeep parameter or adding where clauses for TenantId.

📘

Note:

If you don’t want to archive the logs that are deleted, simple remove the copy clause:
insert into [ArchiveRobotLicenseLogs] select from [RobotLicenseLogs].

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

declare @NumberOfDaysToKeep int
set @NumberOfDaysToKeep = 60
begin transaction
set identity_insert [UiPathOrchestratorArchives].[dbo].[ArchiveRobotLicenseLogs] on
insert into [UiPathOrchestratorArchives].[dbo].[ArchiveRobotLicenseLogs]
([Id],[RobotId],[StartDate],[EndDate],[RobotType],[TenantId],[Scope],[Key],[Slots],
[LicenseKey],[Properties],[ErrorCode],[JobKey])
select
[Id],[RobotId],[StartDate],[EndDate],[RobotType],[TenantId],[Scope],[Key],[Slots],
[LicenseKey],[Properties],[ErrorCode],[JobKey]
from [UiPath].[dbo].[RobotLicenseLogs]
where EndDate is not null
-- and TenantId = 1 -- default tenant
and DateDiff(day, EndDate, GetDate()) > @NumberOfDaysToKeep
delete from [UiPath].[dbo].[RobotLicenseLogs]
where EndDate is not null
-- and TenantId = 1 -- default tenant
and DateDiff(day, EndDate, GetDate()) > @NumberOfDaysToKeep
set identity_insert [UiPathOrchestratorArchives].[dbo].[ArchiveRobotLicenseLogs] off
commit transaction

3.4. TenantNotifications / UserNotifications Cleanup

We recommend deleting the items that are older than 60 days in your TenantNotifications and UserNotifications tables. The example query below deletes items older than 60 days. Adapt the query to your needs, changing the @NumberOfDaysToKeep parameter or adding where clauses for TenantId.

declare @NumberOfDaysToKeep int
set @NumberOfDaysToKeep = 60
begin transaction
-- create a temp table with list of IDs that we want to delete
select ID as IdToDelete into #TempDeletedIds from TenantNotifications
where 1=1
-- and TenantId = 1
and DateDiff(day, CreationTime, GetDate()) > @NumberOfDaysToKeep
-------------------- UserNotifications
insert into [UiPathOrchestratorArchives].[dbo].[ArchiveUserNotifications]
([Id],[UserId],[TenantNotificationId],[State],[CreationTime],[TenantId])
select
[Id],[UserId],[TenantNotificationId],[State],[CreationTime],[TenantId]
from [UiPath].[dbo].[UserNotifications]
where Exists (select 1 from #TempDeletedIds where IdToDelete = TenantNotificationId)
delete from [UiPath].[dbo].[UserNotifications]
where Exists (select 1 from #TempDeletedIds where IdToDelete = TenantNotificationId)
-------------------- TenantNotifications
insert into [UiPathOrchestratorArchives].[dbo].[ArchiveTenantNotifications]
([Id],[TenantId],[NotificationName],[Data],[DataTypeName],[EntityTypeName],
[EntityTypeAssemblyQualifiedName],[EntityId],[Severity],[CreationTime],[CreatorUserId])
select
[Id],[TenantId],[NotificationName],[Data],[DataTypeName],[EntityTypeName]
,[EntityTypeAssemblyQualifiedName],[EntityId],[Severity],[CreationTime],[CreatorUserId]
from [UiPath].[dbo].[TenantNotifications]
where Exists (select 1 from #TempDeletedIds where IdToDelete = [UiPath].[dbo].[TenantNotifications].[Id])
delete from [UiPath].[dbo].[TenantNotifications]
where Exists (select 1 from #TempDeletedIds where IdToDelete = [UiPath].[dbo].[TenantNotifications].[Id])
drop table #TempDeletedIds
commit transaction

3.5. Jobs Cleanup

We recommend deleting the items that are older than 60 days in your Jobs table. The example query below deletes items older than 60 days. Adapt the query to your needs, changing the @NumberOfDaysToKeep parameter or adding where clauses for TenantId.

declare @NumberOfDaysToKeep int
set @NumberOfDaysToKeep = 60
begin transaction
set identity_insert [UiPathOrchestratorArchives].[dbo].[ArchiveJobs] on
insert into [UiPathOrchestratorArchives].[dbo].[ArchiveJobs]
([Id],[TenantId],[Key],[StartTime],[EndTime],[State],[RobotId],[ReleaseId]
,[Source],[BatchExecutionKey],[Info],[IsDeleted],[DeleterUserId],[DeletionTime]
,[LastModificationTime],[LastModifierUserId],[CreationTime],[CreatorUserId]
,[OrganizationUnitId],[StartingScheduleId],[Type],[InputArguments],[OutputArguments],[HostMachineName],[PersistenceId],[ResumeVersion],[SuspendBlobType],[PersistenceLocationVersion],[StopStrategy],[ReleaseVersionId],[EntryPointPath],[JobPriority],[RuntimeType],[MachineId],[RequiresUserInteraction],[ServiceUserName],[ResumeTime],[Reference])
select
[Id],[TenantId],[Key],[StartTime],[EndTime],[State],[RobotId],[ReleaseId]
,[Source],[BatchExecutionKey],[Info],[IsDeleted],[DeleterUserId],[DeletionTime]
,[LastModificationTime],[LastModifierUserId],[CreationTime],[CreatorUserId]
,[OrganizationUnitId],[StartingScheduleId],[Type],[InputArguments],[OutputArguments],[HostMachineName],[PersistenceId],[ResumeVersion],[SuspendBlobType],[PersistenceLocationVersion],[StopStrategy],[ReleaseVersionId],[EntryPointPath],[JobPriority],[RuntimeType],[MachineId],[RequiresUserInteraction],[ServiceUserName],[ResumeTime],[Reference]
from [UiPath].[dbo].[Jobs]
where 1=1
-- and State = 5
/*
  0 = Pending, 1 = Running, 2 = Stopping, 3 = Terminating, 4 = Faulted, 
  5 = Successful, 6 = Stopped, 7 = Suspended, 8 = Resumed
*/
-- and TenantId = 1 -- default tenant
and DateDiff(day, CreationTime, GetDate()) > @NumberOfDaysToKeep
delete from [UiPath].[dbo].[jobs]
where 1=1
-- and State = 5
/*
  0 = Pending, 1 = Running, 2 = Stopping, 3 = Terminating, 4 = Faulted, 
  5 = Successful, 6 = Stopped, 7 = Suspended, 8 = Resumed
*/
-- and TenantId = 1 -- default tenant
and DateDiff(day, CreationTime, GetDate()) > @NumberOfDaysToKeep
set identity_insert [UiPathOrchestratorArchives].[dbo].[Archivejobs] off
commit transaction

3.6. AuditLogs and AuditLogEntities Cleanup

We recommend deleting the items that are older than 60 days in your AuditLogs and AuditLogEntities tables. The example query below deletes items older than 60 days. Adapt the query to your needs, changing the @NumberOfDaysToKeep parameter or adding where clauses for TenantId.

declare @NumberOfDaysToKeep int
set @NumberOfDaysToKeep = 60
begin transaction
-- create temp table with list of IDs that we want to delete
select ID as IdToDelete into #TempDeletedIds from AuditLogs
where 1=1
-- and TenantId = 1
and DateDiff(day, ExecutionTime, GetDate()) > @NumberOfDaysToKeep
-------------------- AuditLogEntities
set identity_insert [UiPathOrchestratorArchives].[dbo].[ArchiveAuditLogEntities] on
insert into [UiPathOrchestratorArchives].[dbo].[ArchiveAuditLogEntities]
([Id],[EntityName],[EntityId],[AuditLogId],[CustomData],[Action],[TenantId])
select
[Id],[EntityName],[EntityId],[AuditLogId],[CustomData],[Action],[TenantId]
from [UiPath].[dbo].[AuditLogEntities]
where Exists (select 1 from #TempDeletedIds where IdToDelete = AuditLogId)
delete from [UiPath].[dbo].[AuditLogEntities]
where Exists (select 1 from #TempDeletedIds where IdToDelete = AuditLogId)
set identity_insert [UiPathOrchestratorArchives].[dbo].[ArchiveAuditLogEntities] off
-------------------- AuditLogs
set identity_insert [UiPathOrchestratorArchives].[dbo].[ArchiveAuditLogs] on
insert into [UiPathOrchestratorArchives].[dbo].[ArchiveAuditLogs]
([Id],[TenantId],[UserId],[ServiceName],[MethodName],[Parameters],[ExecutionTime],[ExecutionDuration]
,[ClientIpAddress],[ClientName],[BrowserInfo],[Exception],[ImpersonatorUserId],[ImpersonatorTenantId]
,[CustomData],[Action],[Component],[DisplayName],[Version],[EntityId],[Discriminator],[ReturnValue],[IsGlobal])
select
[Id],[TenantId],[UserId],[ServiceName],[MethodName],[Parameters],[ExecutionTime],[ExecutionDuration]
,[ClientIpAddress],[ClientName],[BrowserInfo],[Exception],[ImpersonatorUserId],[ImpersonatorTenantId]
,[CustomData],[Action],[Component],[DisplayName],[Version],[EntityId],[Discriminator],[ReturnValue],[IsGlobal]
from [UiPath].[dbo].[AuditLogs]
where Exists (select 1 from #TempDeletedIds where IdToDelete = [UiPath].[dbo].[AuditLogs].[Id])
delete from [UiPath].[dbo].[AuditLogs]
where Exists (select 1 from #TempDeletedIds where IdToDelete = [UiPath].[dbo].[AuditLogs].[Id])
set identity_insert [UiPathOrchestratorArchives].[dbo].[ArchiveAuditLogs] off
drop table #TempDeletedIds
commit transaction

3.7. Actions cleanup

We recommend deleting the completed and soft-deleted actions that are older than 60 days in your Tasks table.
Actions are stored in the Tasks table.
Deletion through UI marks the items as soft-deleted in the Tasks table.

The example query below deletes completed items (Status = 2) older than 60 days and soft-deleted items (IsDeleted = 1).
Adapt the query to your needs, changing the @NumberOfDaysToKeep parameter or adding where clauses for TenantId or OrganizationUnitId.

📘

Note:

If you don’t want to archive the Tasks that are deleted, remove the insert into [ArchiveTasks] select from [Tasks] statement.

declare @NumberOfDaysToKeep int
set @NumberOfDaysToKeep = 60
begin transaction
    set identity_insert [UiPathOrchestratorArchives].[dbo].[ArchiveTasks] on
    insert into [UiPathOrchestratorArchives].[dbo].[ArchiveTasks] ([Id],[Title],[Priority],[Status],[AssignedToUserId],[FormLayout],[Data],[Action],[Type],[TenantId],[OrganizationUnitId],
    [LastModificationTime],[LastModifierUserId],[CreationTime],[CreatorUserId],[IsDeleted],[DeleterUserId],[DeletionTime],[TaskCatalogId],[IsCompleted],[ExternalTag])
    select
    [Id],[Title],[Priority],[Status],[AssignedToUserId],[FormLayout],[Data],[Action],[Type],[TenantId],[OrganizationUnitId],
    [LastModificationTime],[LastModifierUserId],[CreationTime],[CreatorUserId],[IsDeleted],[DeleterUserId],[DeletionTime],[TaskCatalogId],[IsCompleted],[ExternalTag] 
    from [UiPath].[dbo].[Tasks] 
    where 1=1 
    and ((IsDeleted = 1 and DateDiff(day, DeletionTime, GetDate()) > @NumberOfDaysToKeep)
    or (Status = 2
    /*
      0 = Unassigned, 1 = Pending, 2 = Completed
    */
    -- and TenantId = 1 -- default tenant
    -- and OrganizationUnitId = 1 -- switch to the OrganizationUnit for which you want to delete
    and DateDiff(day, LastModificationTime, GetDate()) > @NumberOfDaysToKeep))
    
    delete from [UiPath].[dbo].[Tasks]
    where 1=1 
    and ((IsDeleted = 1 and DateDiff(day, DeletionTime, GetDate()) > @NumberOfDaysToKeep)
    or (Status = 2
    /*
      0 = Unassigned, 1 = Pending, 2 = Completed
    */
    -- and TenantId = 1 -- default tenant
    -- and OrganizationUnitId = 1 -- switch to the OrganizationUnit for which you want to delete
    and DateDiff(day, LastModificationTime, GetDate()) > @NumberOfDaysToKeep))
    set identity_insert [UiPathOrchestratorArchives].[dbo].[ArchiveTasks] off
commit transaction

3.8. Ledger Cleanup

We recommend deleting the items that are older than 7 days in your Ledger and LedgerDeliveries tables. The example query below deletes items older than 7 days and applies a batch size of 1,000 entries.

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

-- Ledger table cleanup
PRINT 'Starting maintenance on [Ledger] table.'

DECLARE @daysToKeep INT
DECLARE @batchSize INT
SET @daysToKeep = 7
SET @batchSize =  1000
DECLARE @currentDate DATETIME
SET @currentDate = GETDATE()
DECLARE @rowsToDelete INT

SET @rowsToDelete =
    (SELECT COUNT(*)
    FROM [dbo].[Ledger]
    WHERE [CreationTime] < @[email protected])

DECLARE @rowsToKeep INT
SET @rowsToKeep =
    (SELECT COUNT(*)
    FROM [dbo].[Ledger]
    WHERE [CreationTime] >= @[email protected])

PRINT 'Rows to delete:' + CAST(@rowsToDelete AS VARCHAR)
PRINT 'Rows to keep:'+ CAST(@rowsToKeep AS VARCHAR)

DECLARE @rowsDeleted INT
SET @rowsDeleted = 1
WHILE (@rowsDeleted > 0)
BEGIN
    DELETE TOP(@batchSize) [dbo].[Ledger] WHERE [CreationTime] < @[email protected]
     OPTION (MAXDOP 1)

    SET @rowsDeleted = @@ROWCOUNT

    SET @rowsToDelete = IIF (@rowsToDelete > @batchSize, @rowsToDelete - @batchSize, 0)

    PRINT 'Deleted ' + CAST(@rowsDeleted AS VARCHAR)+' rows. Remaining to delete:' + CAST(@rowsToDelete AS VARCHAR)
END

--LedgerDeliveries table cleanup
PRINT 'Starting maintenance on [LedgerDeliveries] table.'
SET @rowsToDelete =
    (SELECT COUNT(*)
    FROM [dbo].[LedgerDeliveries]
    WHERE [LastUpdatedTime] < @[email protected])
SET @rowsToKeep =
    (SELECT COUNT(*)
    FROM [dbo].[LedgerDeliveries]
    WHERE [LastUpdatedTime] >= @[email protected])
PRINT 'Rows to delete:' + CAST(@rowsToDelete AS VARCHAR)
PRINT 'Rows to keep:'+ CAST(@rowsToKeep AS VARCHAR)

SET @rowsDeleted = 1
WHILE (@rowsDeleted > 0)
BEGIN
    DELETE TOP(@batchSize) [dbo].[LedgerDeliveries] WHERE [LastUpdatedTime] < @[email protected]
     OPTION (MAXDOP 1)

    SET @rowsDeleted = @@ROWCOUNT
    SET @rowsToDelete = IIF (@rowsToDelete > @batchSize, @rowsToDelete - @batchSize, 0)

    PRINT 'Deleted ' + CAST(@rowsDeleted AS VARCHAR)+' rows. Remaining to delete:' + CAST(@rowsToDelete AS VARCHAR)
END

📘

Note:

You can customize the cleanup interval and the number of entries to be deleted, by modifying the values of @daysToKeep and @batchSize variables.

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

3.10 Sessions

We recommend deleting unattended sessions with [ReportingTime] older than 60 days in your Sessions table. The example query below deletes items older than 60 days. Adapt the query to your needs, changing the @daysToKeep parameter or adding where clauses for TenantId

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

DECLARE @daysToKeep INT = 60

BEGIN TRANSACTION
    SELECT s.[Id] 
    INTO #tempTable 
    FROM [dbo].[Sessions] s 
    LEFT JOIN [dbo].[RobotLicenses] rl ON rl.[SessionId] = s.[Id] AND rl.[Scope] > 1 
    LEFT JOIN [dbo].[ProcessScheduleMachineRobots] p ON p.[SessionId] = s.[Id] 
    WHERE s.[ReportingTime] < GETUTCDATE()[email protected] 
    AND s.[RobotId] IS NULL 
    AND rl.[Id] IS NULL 
    AND p.[Id] IS NULL 
    -- AND s.[TenantId] = 1 -- default tenant
    ORDER BY s.[Id] asc 
    
    DELETE rl  
    FROM [dbo].[RobotLicenses] rl 
    INNER JOIN #tempTable t ON t.[Id] = rl.[SessionId]
    
    DELETE s  
    FROM [dbo].[Sessions] s 
    INNER JOIN #tempTable t ON t.[Id] = s.[Id]
    
    DROP TABLE #tempTable
COMMIT TRANSACTION

Updated about a month ago


Performing database maintenance


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.