Database Maintenance For Orchestrator

News Knowledge Base
1

Procedure for Database maintenance and deletion of data of more than 30 days.

Step1 : Take back up of PROD DB (UiPath) from the database server

Step 2: Run the queries to delete the rows in table

  1. DELETE FROM [dbo]. [AuditLogEntities] WHERE AuditLogId in (select Id from dbo.auditlogs where DateDiff(day, ExecutionTime, GetDate()) > 30)
  2. Delete from dbo.auditlogs where DateDiff(day, ExecutionTime, GetDate()) > 30
  3. DELETE FROM [dbo]. [AuditLogEntities] WHERE AuditLogId < ?????
  4. DELETE FROM [dbo].[jobs] WHERE DateDiff(day, CreationTime, GetDate()) > 30
  5. DELETE FROM [dbo].[QueueItemEvents] WHERE DateDiff(day, CreationTime, GetDate()) > 30
  6. DELETE FROM [dbo].[QueueItems] WHERE DateDiff(day, CreationTime, GetDate()) > 30
  7. DELETE FROM [dbo].[RobotLicenseLogs] WHERE EndDate is not null and DateDiff(day, EndDate, GetDate()) > 30
  8. DELETE FROM [dbo]. [TenantNotifications] WHERE DateDiff(day, CreationTime, GetDate()) > 30
  9. DELETE FROM [dbo]. [UserLoginAttempts] WHERE DateDiff(day, CreationTime, GetDate()) > 30
  10. DELETE FROM [dbo]. [UserNotifications] WHERE DateDiff(day, CreationTime, GetDate()) > 365

Step 3: Rebuild and re-organize indexes - This will be done by Database team. Capture steps with screenshots on rebuild.

In case, if more details is required about Rebuild and Reorganization of indexes, note that Index should be rebuilt when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

Index Rebuild : This process drops the existing Index and Recreates the index.

USE DatabaseName;
GO
ALTER INDEX ALL ON dbo.TableName REBUILD
GO

Index Reorganize : This process physically reorganizes the leaf nodes of the index.

USE DatabaseName;
GO
ALTER INDEX ALL ON dbo.TableName REORGANIZE
GO


Refer Maintenance Considerations for Deleting Old Data Periodically guide for the maintenance consideration from the UiPath.

3 Likes
2

A post was split to a new topic: Database maintenance and deletion of data of more than 30 days

3

how can i remove the data using the new script in the documentation ?