- Overview
- Requirements
- Installation
- Post-installation
- Cluster administration
- Monitoring and alerting
- Migration and upgrade
- Product-specific configuration
- Best practices and maintenance
- Installation Best Practices
- Performing database maintenance
- Performing yearly certificate maintenance
- Troubleshooting
- How to Troubleshoot Services During Installation
- How to Uninstall the Cluster
- How to clean up offline artifacts to improve disk space
- How to Disable TLS 1.0 and 1.1
- How to enable Istio logging
- How to manually clean up logs
- How to clean up old logs stored in the sf-logs bundle
- How to debug failed Automation Suite installations
- How to disable NIC checksum offloading
- Unable to run an offline installation on RHEL 8.4 OS
- Error in Downloading the Bundle
- Offline installation fails because of missing binary
- Certificate issue in offline installation
- SQL connection string validation error
- Failure After Certificate Update
- Automation Suite Requires Backlog_wait_time to Be Set 1
- Cannot Log in After Migration
- Setting a timeout interval for the management portals
- Update the underlying directory connections
- Kinit: Cannot Find KDC for Realm <AD Domain> While Getting Initial Credentials
- Kinit: Keytab Contains No Suitable Keys for *** While Getting Initial Credentials
- GSSAPI Operation Failed With Error: An Invalid Status Code Was Supplied (Client's Credentials Have Been Revoked).
- Login Failed for User <ADDOMAIN><aduser>. Reason: The Account Is Disabled.
- Alarm Received for Failed Kerberos-tgt-update Job
- SSPI Provider: Server Not Found in Kerberos Database
- Failure to get the sandbox image
- Pods not showing in ArgoCD UI
- Redis Probe Failure
- RKE2 Server Fails to Start
- Secret Not Found in UiPath Namespace
- ArgoCD goes into progressing state after first installation
- Unexpected Inconsistency; Run Fsck Manually
- Missing Self-heal-operator and Sf-k8-utils Repo
- Degraded MongoDB or Business Applications After Cluster Restore
- Unhealthy Services After Cluster Restore or Rollback
- Using the Automation Suite Diagnostics Tool
- Using the Automation Suite Support Bundle Tool
- Exploring Logs
Performing database maintenance
It is important to keep your databases free from clutter. To do this, we recommend:
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.
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.
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.
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.
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.
You can download the Orchestrator database maintenance scripts from the Customer Portal.
SQL script
Prerequisites
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
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.
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.
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';
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.
The PowerShell script is compatible with Powershell 5.1 and can be used with Azure Automation runbooks.
Prerequisites
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
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>'
.\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>
<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 |
---|---|---|
|
The maximum number of minutes that the script is allowed to execute for all tables during one run. |
Must be higher than 1. |
|
The name of the table containing the data that you want to delete. |
Example:
QueueItems |
|
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.
|
|
The ID of the column which contains the data you want to delete. |
Example:
Id from the QueueItems table
|
|
This differs depending on the data type. It is used in combination with
daysOld .
|
Example:
CreationTime for QueueItems |
|
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.
|
|
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. |