订阅

UiPath Installation and Upgrade

UiPath 安装和升级指南

Maintenance Considerations

It is important to keep your Orchestrator database 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.

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. 创建一个新的调用数据库,例如 UiPathOrchestratorArchives
create database UiPathOrchestratorArchives
  1. 创建以下备份表:
    2.1.ArchiveLogs,其结构与 Logs 表相同:
select * into [UiPathOrchestratorArchives].[dbo].[ArchiveLogs] from [UiPath].[dbo].[Logs] where 1=2

2.2. ArchiveQueueItemsArchiveQueueItemEventsArchiveQueueItemComments,其结构与 QueueItemsQueueItemsEventsQueueItemsComments表相同:

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,其结构与 RobotLogs 表相同:

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

2.4.TenantNotificationsArchiveUserNotificationsArchive,其结构与 TenantNotificationsUserNotifications 表相同:

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,其结构与 Jobs 表相同:

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

2.5.ArchiveAuditLogsArchiveAuditLogEntities,其结构与 AuditLogAuditLogEntities 表相同:

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,其结构与 Tasks 表相同:

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

若使用以下查询,则在删除旧数据前,会将旧数据先复制到这些档案中。

Deleting Old Data Periodically

🚧

重要

  • 删除旧数据时,请勿重置 ID 列(主键)的计数。

  • 在运行以下脚本之前,请确保使它们适应您的环境。

队列项目

例如,要删除 status = 3 超过 60 天的已成功处理的队列项目以及相关事件和注释,请使用以下查询。作为可选项,您也可以为 TenantId 包含一个 where 子句。

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],[QueueDefinitionId],[ExternalClientId])
select
[Id],[TenantId],[OrganizationUnitId],[QueueItemId],[TimeStamp],[IsDeleted],[DeleterUserId],[DeletionTime],[Action],[UserId],[Status],[ReviewStatus],[ReviewerUserId],[CreationTime],[CreatorUserId],[QueueDefinitionId],[ExternalClientId]
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],[QueueDefinitionId])
select
[Id],[TenantId],[OrganizationUnitId],[QueueItemId],[Text],[IsDeleted],[DeleterUserId],[DeletionTime],[LastModificationTime],[LastModifierUserId],[CreationTime],[CreatorUserId],[QueueDefinitionId]
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],[Encrypted]
)
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],[Encrypted]
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

Logged Messages

我们建议删除超过 60 天的日志消息。下面的示例查询将删除超过 60 天信息级别为 Info 的旧消息。您可以根据需求调整查询,更改 @NumberOfDaysToKeep 参数或为 LevelTenantId 添加 where 子句。

📘

备注:

如果您不想归档已删除的日志,只需删除 copy 子句即可:
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

RobotLicense 日志

我们建议删除 RobotLicenseLogs 表中超过 60 天的项目。下面的示例查询将删除超过 60 天的项目。您可以根据需求调整查询,更改 @NumberOfDaysToKeep 参数或为 TenantId 添加 where子句。

📘

备注:

如果您不想归档已删除的日志,只需删除 copy 子句即可:
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

租户通知/用户通知清除

我们建议删除 TenantNotificationsUserNotifications 表中超过 60 天的项目。下面的示例查询将删除超过 60 天的项目。您可以根据需求调整查询,更改 @NumberOfDaysToKeep 参数或为 TenantId 添加 where 子句。

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

作业清理

我们建议删除 Jobs 表中超过 60 天的项目。下面的示例查询将删除超过 60 天的项目。您可以根据需求调整查询,更改 @NumberOfDaysToKeep 参数或为 TenantId 添加 where子句。

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], [ProcessType], [ResumeOnSameContext], [TargetFramework], [VideoOptions], [RunAsMe])
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], [ProcessType], [ResumeOnSameContext], [TargetFramework], [VideoOptions], [RunAsMe]
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

AuditLogs 和 AuditLogEntities 清理

我们建议删除 AuditLogsAuditLogEntities 表中超过 60 天的项目。下面的示例查询将删除超过 60 天的项目。您可以根据需求调整查询,更改 @NumberOfDaysToKeep 参数或为 TenantId 添加 where 子句。

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],[ReturnValue],[IsGlobal])
select
[Id],[TenantId],[UserId],[ServiceName],[MethodName],[Parameters],[ExecutionTime],[ExecutionDuration]
,[ClientIpAddress],[ClientName],[BrowserInfo],[Exception],[ImpersonatorUserId],[ImpersonatorTenantId]
,[CustomData],[Action],[Component],[DisplayName],[Version],[EntityId],[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

操作清理

我们建议删除您的 Tasks 表中超过 60 天的已完成和软删除项目。
Actions are stored in the Tasks table.
Deletion through UI marks the items as soft-deleted in the Tasks table.

下方的示例查询将删除已完成超过 60 天的项目 (Status = 2) 和软删除的项目 (IsDeleted = 1)。
您可以根据需求调整查询,更改 @NumberOfDaysToKeep 参数或为 TenantIdOrganizationUnitId 添加 where 子句。

📘

备注:

如果您不想存档已删除的任务,请删除 insert into [ArchiveTasks] select from [Tasks] 语句。

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

会话清理

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

分类账清理

我们建议删除 LedgerLedgerDeliveries 表中超过 7 天的项目。下面的示例查询将删除超过 7 天的项目,并应用 1,000 个条目的批处理大小。

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

📘

备注:

您可以通过修改 @daysToKeep@batchSize 变量的值来自定义清理间隔和要删除的条目数。

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

我们建议对 SQL Server 数据库实施定期备份,例如每周完整备份或每日增量备份。

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

约一个月前更新


Maintenance Considerations


建议的编辑仅限用于 API 参考页面

您只能建议对 Markdown 正文内容进行编辑,而不能建议对 API 规范进行编辑。