Update to version 2023.1.970 failed

Hi,

I tried upgrading our Octopus Deploy installation from version Octopus.2022.3.10674 to Octopus.2023.1.9720. During the installation I encountered the following error(see attached file)
octo_errorlog.txt (38.2 KB)

After rolling back to version Octopus.2022.3.10674 I ran the diagnostics tool and got the following errors:

Missing item: IDX dbo.IX_Blob_BlobId BlobId
Missing item: IDX dbo.IX_Blob_BlobId CLUSTERED 0
Missing item: IDX dbo.IX_Blob_BlobId ExtensionId
Missing item: IDX dbo.IX_BuildInformationCreatedDate CreatedDate
Missing item: IDX dbo.IX_BuildInformationCreatedDate NONCLUSTERED 0
Missing item: IDX dbo.IX_BuildInformationPackageIdPerSpace NONCLUSTERED 0
Missing item: IDX dbo.IX_BuildInformationPackageIdPerSpace PackageId
Missing item: IDX dbo.IX_BuildInformationPackageIdPerSpace SpaceId
Missing item: IDX dbo.IX_DeploymentSettings_DataVersion DataVersion
Missing item: IDX dbo.IX_DeploymentSettings_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_DeploymentSettings_SpaceId_ProjectId CLUSTERED 0
Missing item: IDX dbo.IX_DeploymentSettings_SpaceId_ProjectId ProjectId
Missing item: IDX dbo.IX_DeploymentSettings_SpaceId_ProjectId SpaceId
Missing item: IDX dbo.IX_DeploymentTargetMetadataInspection_CapturedAt CapturedAt
Missing item: IDX dbo.IX_DeploymentTargetMetadataInspection_CapturedAt NONCLUSTERED 0
Missing item: IDX dbo.IX_DynamicInfrastructureLifecycle_BusinessProcess BusinessProcessState
Missing item: IDX dbo.IX_DynamicInfrastructureLifecycle_BusinessProcess LastModified
Missing item: IDX dbo.IX_DynamicInfrastructureLifecycle_BusinessProcess NONCLUSTERED 0
Missing item: IDX dbo.IX_EventRelatedDocument_EventId_RelatedDocumentIdPrefix EventId
Missing item: IDX dbo.IX_EventRelatedDocument_EventId_RelatedDocumentIdPrefix NONCLUSTERED 0
Missing item: IDX dbo.IX_EventRelatedDocument_EventId_RelatedDocumentIdPrefix RelatedDocumentId
Missing item: IDX dbo.IX_EventRelatedDocument_EventId_RelatedDocumentIdPrefix RelatedDocumentIdPrefix
Missing item: IDX dbo.IX_EventRelatedDocument_RelatedDocumentIdPrefix EventId
Missing item: IDX dbo.IX_EventRelatedDocument_RelatedDocumentIdPrefix NONCLUSTERED 0
Missing item: IDX dbo.IX_EventRelatedDocument_RelatedDocumentIdPrefix RelatedDocumentId
Missing item: IDX dbo.IX_EventRelatedDocument_RelatedDocumentIdPrefix RelatedDocumentIdPrefix
Missing item: IDX dbo.IX_GitCredential_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_GitCredential_SpaceId SpaceId
Missing item: IDX dbo.IX_HalibutMessageQueueItem_SequenceNumber CLUSTERED 0
Missing item: IDX dbo.IX_HalibutMessageQueueItem_SequenceNumber Direction
Missing item: IDX dbo.IX_HalibutMessageQueueItem_SequenceNumber Endpoint
Missing item: IDX dbo.IX_HalibutMessageQueueItem_SequenceNumber SequenceNumber
Missing item: IDX dbo.IX_InsightsReport_SpaceId CLUSTERED 0
Missing item: IDX dbo.IX_InsightsReport_SpaceId SpaceId
Missing item: IDX dbo.IX_MachineHealthCheck_BusinessProcess BusinessProcessState
Missing item: IDX dbo.IX_MachineHealthCheck_BusinessProcess LastModified
Missing item: IDX dbo.IX_MachineHealthCheck_BusinessProcess NONCLUSTERED 0
Missing item: IDX dbo.IX_MachineScriptTask_BusinessProcess BusinessProcessState
Missing item: IDX dbo.IX_MachineScriptTask_BusinessProcess LastModified
Missing item: IDX dbo.IX_MachineScriptTask_BusinessProcess NONCLUSTERED 0
Missing item: IDX dbo.IX_MessageBusCursor_ConsumerGroupId ConsumerGroupId
Missing item: IDX dbo.IX_MessageBusCursor_ConsumerGroupId NONCLUSTERED 0
Missing item: IDX dbo.IX_MessageBusEvent_SequenceNumber CLUSTERED 0
Missing item: IDX dbo.IX_MessageBusEvent_SequenceNumber SequenceNumber
Missing item: IDX dbo.IX_ProjectTrigger_RunbookId NONCLUSTERED 0
Missing item: IDX dbo.IX_ProjectTrigger_RunbookId RunbookId
Missing item: IDX dbo.IX_ProjectTrigger_RunbookId SpaceId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled Assembled
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled ChannelId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled Id
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled NONCLUSTERED 0
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled ProjectDeploymentProcessSnapshotId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled ProjectId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled ProjectVariableSetSnapshotId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled SpaceId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled Version
Missing item: IDX dbo.IX_Runbook_DataVersion DataVersion
Missing item: IDX dbo.IX_Runbook_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Runbook_DataVersion SpaceId
Missing item: IDX dbo.IX_Runbook_ProjectId NONCLUSTERED 0
Missing item: IDX dbo.IX_Runbook_ProjectId ProjectId
Missing item: IDX dbo.IX_Runbook_Published_PublishedRunbookSnapshotId NONCLUSTERED 0
Missing item: IDX dbo.IX_Runbook_Published_PublishedRunbookSnapshotId PublishedRunbookSnapshotId
Missing item: IDX dbo.IX_Runbook_Published_PublishedRunbookSnapshotId SpaceId
Missing item: IDX dbo.IX_Runbook_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Runbook_SpaceId SpaceId
Missing item: IDX dbo.IX_RunbookProcess_ProjectId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookProcess_ProjectId ProjectId
Missing item: IDX dbo.IX_RunbookProcess_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookProcess_SpaceId SpaceId
Missing item: IDX dbo.IX_RunbookRun_Index Created
Missing item: IDX dbo.IX_RunbookRun_Index EnvironmentId
Missing item: IDX dbo.IX_RunbookRun_Index Id
Missing item: IDX dbo.IX_RunbookRun_Index Name
Missing item: IDX dbo.IX_RunbookRun_Index NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_Index ProjectId
Missing item: IDX dbo.IX_RunbookRun_Index RunbookSnapshotId
Missing item: IDX dbo.IX_RunbookRun_Index TaskId
Missing item: IDX dbo.IX_RunbookRun_ProjectId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_ProjectId ProjectId
Missing item: IDX dbo.IX_RunbookRun_RunbookId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_RunbookId RunbookId
Missing item: IDX dbo.IX_RunbookRun_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_SpaceId SpaceId
Missing item: IDX dbo.IX_RunbookRun_TenantId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_TenantId TenantId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory Created
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory DeployedBy
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory EnvironmentId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory Name
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory ProjectId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory RunbookId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory RunbookSnapshotId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory TaskId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory TenantId
Missing item: IDX dbo.IX_RunbookRunHistory_IsPublished IsPublished
Missing item: IDX dbo.IX_RunbookRunHistory_IsPublished NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRunRelatedMachine_RunbookRun_Machine MachineId
Missing item: IDX dbo.IX_RunbookRunRelatedMachine_RunbookRun_Machine NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRunRelatedMachine_RunbookRun_Machine RunbookRunId
Missing item: IDX dbo.IX_RunbookSnapshot_Assembled Assembled
Missing item: IDX dbo.IX_RunbookSnapshot_Assembled NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_DataVersion DataVersion
Missing item: IDX dbo.IX_RunbookSnapshot_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_FrozenRunbookProcessId FrozenRunbookProcessId
Missing item: IDX dbo.IX_RunbookSnapshot_FrozenRunbookProcessId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_ProjectId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_ProjectId ProjectId
Missing item: IDX dbo.IX_RunbookSnapshot_RunbookId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_RunbookId RunbookId
Missing item: IDX dbo.IX_RunbookSnapshot_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_SpaceId SpaceId
Missing item: IDX dbo.IX_ScopedUserRole_SpaceDataVersion DataVersion
Missing item: IDX dbo.IX_ScopedUserRole_SpaceDataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_ScopedUserRole_SpaceDataVersion SpaceId
Missing item: IDX dbo.IX_ServerTask_BusinessProcess BusinessProcessState
Missing item: IDX dbo.IX_ServerTask_BusinessProcess LastModified
Missing item: IDX dbo.IX_ServerTask_BusinessProcess NONCLUSTERED 0
Missing item: IDX dbo.IX_ServerTask_BusinessProcess SpaceId
Missing item: IDX dbo.IX_Space_DataVersion DataVersion
Missing item: IDX dbo.IX_Space_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Subscription_DataVersion DataVersion
Missing item: IDX dbo.IX_Subscription_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_TaskLogEntry_CorrelationId_OccurredAt CorrelationId
Missing item: IDX dbo.IX_TaskLogEntry_CorrelationId_OccurredAt NONCLUSTERED 0
Missing item: IDX dbo.IX_TaskLogEntry_CorrelationId_OccurredAt OccurredAt
Missing item: IDX dbo.IX_TaskLogEntry_SequenceNumber CLUSTERED 0
Missing item: IDX dbo.IX_TaskLogEntry_SequenceNumber SequenceNumber
Missing item: IDX dbo.IX_TaskLogRelationship_CorrelationId CorrelationId
Missing item: IDX dbo.IX_TaskLogRelationship_CorrelationId NONCLUSTERED 0
Missing item: IDX dbo.IX_TaskLogRelationship_SequenceNumber CLUSTERED 0
Missing item: IDX dbo.IX_TaskLogRelationship_SequenceNumber SequenceNumber
Missing item: IDX dbo.IX_TaskLogRelationships_ParentCorrelationId NONCLUSTERED 0
Missing item: IDX dbo.IX_TaskLogRelationships_ParentCorrelationId ParentCorrelationId
Missing item: IDX dbo.IX_Team_SpaceDataVersion DataVersion
Missing item: IDX dbo.IX_Team_SpaceDataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Team_SpaceDataVersion SpaceId
Missing item: IDX dbo.IX_Tenant_ClonedFromTenantId ClonedFromTenantId
Missing item: IDX dbo.IX_Tenant_ClonedFromTenantId NONCLUSTERED 0
Missing item: IDX dbo.IX_User_DataVersion DataVersion
Missing item: IDX dbo.IX_User_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_UserRole_DataVersion DataVersion
Missing item: IDX dbo.IX_UserRole_DataVersion NONCLUSTERED 0
Missing item: IDX Insights.IX_InsightsDeployment_Index CLUSTERED 0
Missing item: IDX Insights.IX_InsightsDeployment_Index CompletedTime
Missing item: IDX Insights.IX_InsightsDeployment_Index EnvironmentId
Missing item: IDX Insights.IX_InsightsDeployment_Index ProjectId
Missing item: IDX Insights.IX_InsightsDeployment_Index SpaceId
Missing item: IDX Insights.IX_InsightsRelease_Index CLUSTERED 0
Missing item: IDX Insights.IX_InsightsRelease_Index ProjectId
Missing item: IDX Insights.IX_InsightsRelease_Index SpaceId 

Please advise on how to proceed.

Regards,
Karl

Hey Karl,

Thanks for reaching out and for all of the information.

Unfortunately, it looks like you’re running into this bug here: Octopus Server - Failed Upgrade to 2022.4 due to Missing Index - IX_HalibutMessageQueueItem_SequenceNumber · Issue #8015 · OctopusDeploy/Issues · GitHub

There is a workaround in the issue that you can utilize to get beyond the error.

If you prefer to stay on 2022.3.10674, I can get you the query to recreate all of those missing indexes, but if you are going to upgrade it might be best to wait to see your system integrity check after you’ve used the workaround and upgraded.

Please let me know which direction you’d like to go with this one and I can help you out either way.

Best,
Jeremy

Hi,

Thanks for that link. I’ve proceeded to update Octopus using the provided workaround. Running a diagnostics check after update yielded the following errors:

Missing item: IDX dbo.IX_Blob_BlobId BlobId
Missing item: IDX dbo.IX_Blob_BlobId CLUSTERED 0
Missing item: IDX dbo.IX_Blob_BlobId ExtensionId
Missing item: IDX dbo.IX_BuildInformationCreatedDate CreatedDate
Missing item: IDX dbo.IX_BuildInformationCreatedDate NONCLUSTERED 0
Missing item: IDX dbo.IX_BuildInformationPackageIdPerSpace NONCLUSTERED 0
Missing item: IDX dbo.IX_BuildInformationPackageIdPerSpace PackageId
Missing item: IDX dbo.IX_BuildInformationPackageIdPerSpace SpaceId
Missing item: IDX dbo.IX_DeploymentSettings_DataVersion DataVersion
Missing item: IDX dbo.IX_DeploymentSettings_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_DeploymentSettings_SpaceId_ProjectId CLUSTERED 0
Missing item: IDX dbo.IX_DeploymentSettings_SpaceId_ProjectId ProjectId
Missing item: IDX dbo.IX_DeploymentSettings_SpaceId_ProjectId SpaceId
Missing item: IDX dbo.IX_DeploymentTargetMetadataInspection_CapturedAt CapturedAt
Missing item: IDX dbo.IX_DeploymentTargetMetadataInspection_CapturedAt NONCLUSTERED 0
Missing item: IDX dbo.IX_DynamicInfrastructureLifecycle_BusinessProcess BusinessProcessState
Missing item: IDX dbo.IX_DynamicInfrastructureLifecycle_BusinessProcess LastModified
Missing item: IDX dbo.IX_DynamicInfrastructureLifecycle_BusinessProcess NONCLUSTERED 0
Missing item: IDX dbo.IX_EventRelatedDocument_EventId_RelatedDocumentIdPrefix EventId
Missing item: IDX dbo.IX_EventRelatedDocument_EventId_RelatedDocumentIdPrefix NONCLUSTERED 0
Missing item: IDX dbo.IX_EventRelatedDocument_EventId_RelatedDocumentIdPrefix RelatedDocumentId
Missing item: IDX dbo.IX_EventRelatedDocument_EventId_RelatedDocumentIdPrefix RelatedDocumentIdPrefix
Missing item: IDX dbo.IX_EventRelatedDocument_RelatedDocumentIdPrefix EventId
Missing item: IDX dbo.IX_EventRelatedDocument_RelatedDocumentIdPrefix NONCLUSTERED 0
Missing item: IDX dbo.IX_EventRelatedDocument_RelatedDocumentIdPrefix RelatedDocumentId
Missing item: IDX dbo.IX_EventRelatedDocument_RelatedDocumentIdPrefix RelatedDocumentIdPrefix
Missing item: IDX dbo.IX_GitCredential_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_GitCredential_SpaceId SpaceId
Missing item: IDX dbo.IX_InsightsReport_SpaceId CLUSTERED 0
Missing item: IDX dbo.IX_InsightsReport_SpaceId SpaceId
Missing item: IDX dbo.IX_MachineHealthCheck_BusinessProcess BusinessProcessState
Missing item: IDX dbo.IX_MachineHealthCheck_BusinessProcess LastModified
Missing item: IDX dbo.IX_MachineHealthCheck_BusinessProcess NONCLUSTERED 0
Missing item: IDX dbo.IX_MachineScriptTask_BusinessProcess BusinessProcessState
Missing item: IDX dbo.IX_MachineScriptTask_BusinessProcess LastModified
Missing item: IDX dbo.IX_MachineScriptTask_BusinessProcess NONCLUSTERED 0
Missing item: IDX dbo.IX_MessageBusCursor_ConsumerGroupId ConsumerGroupId
Missing item: IDX dbo.IX_MessageBusCursor_ConsumerGroupId NONCLUSTERED 0
Missing item: IDX dbo.IX_MessageBusEvent_SequenceNumber CLUSTERED 0
Missing item: IDX dbo.IX_MessageBusEvent_SequenceNumber SequenceNumber
Missing item: IDX dbo.IX_ProjectTrigger_RunbookId NONCLUSTERED 0
Missing item: IDX dbo.IX_ProjectTrigger_RunbookId RunbookId
Missing item: IDX dbo.IX_ProjectTrigger_RunbookId SpaceId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled Assembled
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled ChannelId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled Id
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled NONCLUSTERED 0
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled ProjectDeploymentProcessSnapshotId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled ProjectId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled ProjectVariableSetSnapshotId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled SpaceId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled Version
Missing item: IDX dbo.IX_Runbook_DataVersion DataVersion
Missing item: IDX dbo.IX_Runbook_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Runbook_DataVersion SpaceId
Missing item: IDX dbo.IX_Runbook_ProjectId NONCLUSTERED 0
Missing item: IDX dbo.IX_Runbook_ProjectId ProjectId
Missing item: IDX dbo.IX_Runbook_Published_PublishedRunbookSnapshotId NONCLUSTERED 0
Missing item: IDX dbo.IX_Runbook_Published_PublishedRunbookSnapshotId PublishedRunbookSnapshotId
Missing item: IDX dbo.IX_Runbook_Published_PublishedRunbookSnapshotId SpaceId
Missing item: IDX dbo.IX_Runbook_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Runbook_SpaceId SpaceId
Missing item: IDX dbo.IX_RunbookProcess_ProjectId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookProcess_ProjectId ProjectId
Missing item: IDX dbo.IX_RunbookProcess_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookProcess_SpaceId SpaceId
Missing item: IDX dbo.IX_RunbookRun_Index Created
Missing item: IDX dbo.IX_RunbookRun_Index EnvironmentId
Missing item: IDX dbo.IX_RunbookRun_Index Id
Missing item: IDX dbo.IX_RunbookRun_Index Name
Missing item: IDX dbo.IX_RunbookRun_Index NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_Index ProjectId
Missing item: IDX dbo.IX_RunbookRun_Index RunbookSnapshotId
Missing item: IDX dbo.IX_RunbookRun_Index TaskId
Missing item: IDX dbo.IX_RunbookRun_ProjectId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_ProjectId ProjectId
Missing item: IDX dbo.IX_RunbookRun_RunbookId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_RunbookId RunbookId
Missing item: IDX dbo.IX_RunbookRun_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_SpaceId SpaceId
Missing item: IDX dbo.IX_RunbookRun_TenantId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_TenantId TenantId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory Created
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory DeployedBy
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory EnvironmentId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory Name
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory ProjectId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory RunbookId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory RunbookSnapshotId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory TaskId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory TenantId
Missing item: IDX dbo.IX_RunbookRunHistory_IsPublished IsPublished
Missing item: IDX dbo.IX_RunbookRunHistory_IsPublished NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRunRelatedMachine_RunbookRun_Machine MachineId
Missing item: IDX dbo.IX_RunbookRunRelatedMachine_RunbookRun_Machine NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRunRelatedMachine_RunbookRun_Machine RunbookRunId
Missing item: IDX dbo.IX_RunbookSnapshot_Assembled Assembled
Missing item: IDX dbo.IX_RunbookSnapshot_Assembled NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_DataVersion DataVersion
Missing item: IDX dbo.IX_RunbookSnapshot_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_FrozenRunbookProcessId FrozenRunbookProcessId
Missing item: IDX dbo.IX_RunbookSnapshot_FrozenRunbookProcessId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_ProjectId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_ProjectId ProjectId
Missing item: IDX dbo.IX_RunbookSnapshot_RunbookId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_RunbookId RunbookId
Missing item: IDX dbo.IX_RunbookSnapshot_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_SpaceId SpaceId
Missing item: IDX dbo.IX_ScopedUserRole_SpaceDataVersion DataVersion
Missing item: IDX dbo.IX_ScopedUserRole_SpaceDataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_ScopedUserRole_SpaceDataVersion SpaceId
Missing item: IDX dbo.IX_ServerTask_BusinessProcess BusinessProcessState
Missing item: IDX dbo.IX_ServerTask_BusinessProcess LastModified
Missing item: IDX dbo.IX_ServerTask_BusinessProcess NONCLUSTERED 0
Missing item: IDX dbo.IX_ServerTask_BusinessProcess SpaceId
Missing item: IDX dbo.IX_Subscription_DataVersion DataVersion
Missing item: IDX dbo.IX_Subscription_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Team_SpaceDataVersion DataVersion
Missing item: IDX dbo.IX_Team_SpaceDataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Team_SpaceDataVersion SpaceId
Missing item: IDX dbo.IX_Tenant_ClonedFromTenantId ClonedFromTenantId
Missing item: IDX dbo.IX_Tenant_ClonedFromTenantId NONCLUSTERED 0
Missing item: IDX dbo.IX_User_DataVersion DataVersion
Missing item: IDX dbo.IX_User_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_UserRole_DataVersion DataVersion
Missing item: IDX dbo.IX_UserRole_DataVersion NONCLUSTERED 0
Missing item: IDX Insights.IX_InsightsDeployment_Index CLUSTERED 0
Missing item: IDX Insights.IX_InsightsDeployment_Index CompletedTime
Missing item: IDX Insights.IX_InsightsDeployment_Index EnvironmentId
Missing item: IDX Insights.IX_InsightsDeployment_Index ProjectId
Missing item: IDX Insights.IX_InsightsDeployment_Index SpaceId
Missing item: IDX Insights.IX_InsightsRelease_Index CLUSTERED 0
Missing item: IDX Insights.IX_InsightsRelease_Index ProjectId
Missing item: IDX Insights.IX_InsightsRelease_Index SpaceId 

Thanks!

Hey @Wildpipe,

Sorry it has taken awhile to get back to you and great news you managed to upgrade!

If you did upgrade to 2023.1.9720 the SQL query to re-create those missing indexes is below, please do not run it if you upgraded to another version though, let me know if that is the case and I will get you another list for the indexes. Another note for any customers that see this, the list below is instance version specific and also specific to the list Karl sent us, if you are having missing index warnings in your System Integrity check please send us an email at support@octopus.com or raise a forum ticket and we will send you a bespoke list.

 CREATE  UNIQUE CLUSTERED INDEX IX_Blob_BlobId ON dbo.Blob (  ExtensionId ASC  , BlobId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_BuildInformationCreatedDate ON dbo.BuildInformation (  CreatedDate ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_BuildInformationPackageIdPerSpace ON dbo.BuildInformation (  SpaceId ASC  , PackageId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_DeploymentSettings_DataVersion ON dbo.DeploymentSettings (  DataVersion ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE  UNIQUE CLUSTERED INDEX IX_DeploymentSettings_SpaceId_ProjectId ON dbo.DeploymentSettings (  SpaceId ASC  , ProjectId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_DeploymentTargetMetadataInspection_CapturedAt ON dbo.DeploymentTargetMetadataInspection (  CapturedAt ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_DynamicInfrastructureLifecycle_BusinessProcess ON dbo.DynamicInfrastructureLifecycle (  LastModified DESC  , BusinessProcessState ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_EventRelatedDocument_EventId_RelatedDocumentIdPrefix ON dbo.EventRelatedDocument (  EventId ASC  , RelatedDocumentIdPrefix ASC  )   INCLUDE ( RelatedDocumentId )  WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_EventRelatedDocument_RelatedDocumentIdPrefix ON dbo.EventRelatedDocument (  RelatedDocumentIdPrefix ASC  )   INCLUDE ( EventId , RelatedDocumentId )  WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_GitCredential_SpaceId ON dbo.GitCredential (  SpaceId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE CLUSTERED INDEX IX_InsightsReport_SpaceId ON dbo.InsightsReport (  SpaceId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_MachineHealthCheck_BusinessProcess ON dbo.MachineHealthCheck (  LastModified DESC  , BusinessProcessState ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_MachineScriptTask_BusinessProcess ON dbo.MachineScriptTask (  LastModified DESC  , BusinessProcessState ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_MessageBusCursor_ConsumerGroupId ON dbo.MessageBusCursor (  ConsumerGroupId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE CLUSTERED INDEX IX_MessageBusEvent_SequenceNumber ON dbo.MessageBusEvent (  SequenceNumber ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_ProjectTrigger_RunbookId ON dbo.ProjectTrigger (  SpaceId ASC  , RunbookId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_Release_SpaceId_ProjectId_ChannelId_Assembled ON dbo.Release (  SpaceId ASC  , ProjectId ASC  , ChannelId ASC  , Assembled DESC  )   INCLUDE ( Id , ProjectDeploymentProcessSnapshotId , ProjectVariableSetSnapshotId , Version )  WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_Runbook_DataVersion ON dbo.Runbook (  SpaceId ASC  , DataVersion ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_Runbook_ProjectId ON dbo.Runbook (  ProjectId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_Runbook_Published_PublishedRunbookSnapshotId ON dbo.Runbook (  SpaceId ASC  , PublishedRunbookSnapshotId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_Runbook_SpaceId ON dbo.Runbook (  SpaceId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_RunbookProcess_ProjectId ON dbo.RunbookProcess (  ProjectId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_RunbookProcess_SpaceId ON dbo.RunbookProcess (  SpaceId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_RunbookRun_Index ON dbo.RunbookRun (  RunbookSnapshotId ASC  , TaskId ASC  , EnvironmentId ASC  )   INCLUDE ( Created , Id , Name , ProjectId )  WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_RunbookRun_ProjectId ON dbo.RunbookRun (  ProjectId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_RunbookRun_RunbookId ON dbo.RunbookRun (  RunbookId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_RunbookRun_SpaceId ON dbo.RunbookRun (  SpaceId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_RunbookRun_TenantId ON dbo.RunbookRun (  TenantId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_RunbookRun_UpdateRunbookRunHistory ON dbo.RunbookRun (  TaskId ASC  )   INCLUDE ( Created , DeployedBy , EnvironmentId , Name , ProjectId , RunbookId , RunbookSnapshotId , TenantId )  WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_RunbookRunHistory_IsPublished ON dbo.RunbookRunHistory (  IsPublished ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_RunbookRunRelatedMachine_RunbookRun_Machine ON dbo.RunbookRunRelatedMachine (  MachineId ASC  )   INCLUDE ( RunbookRunId )  WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_RunbookSnapshot_Assembled ON dbo.RunbookSnapshot (  Assembled DESC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_RunbookSnapshot_DataVersion ON dbo.RunbookSnapshot (  DataVersion ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_RunbookSnapshot_FrozenRunbookProcessId ON dbo.RunbookSnapshot (  FrozenRunbookProcessId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_RunbookSnapshot_ProjectId ON dbo.RunbookSnapshot (  ProjectId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_RunbookSnapshot_RunbookId ON dbo.RunbookSnapshot (  RunbookId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_RunbookSnapshot_SpaceId ON dbo.RunbookSnapshot (  SpaceId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_ScopedUserRole_SpaceDataVersion ON dbo.ScopedUserRole (  SpaceId ASC  , DataVersion ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_ServerTask_BusinessProcess ON dbo.ServerTask (  SpaceId ASC  , LastModified ASC  , BusinessProcessState ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_Subscription_DataVersion ON dbo.Subscription (  DataVersion ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_Team_SpaceDataVersion ON dbo.Team (  SpaceId ASC  , DataVersion ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_Tenant_ClonedFromTenantId ON dbo.Tenant (  ClonedFromTenantId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_User_DataVersion ON dbo.[User] (  DataVersion ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_UserRole_DataVersion ON dbo.UserRole (  DataVersion ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ]

Let me know Karl if that fixes the issue, please also take a DB backup before running this SQL script just incase you need to revert back to the backup.

I look forward to hearing from you,
Kind Regards,
Clare

Hey,

I did indeed upgrade to 2023.1.9720. I ran the queries you provided and then reran the diagnostics. This time we got the following errors:

Missing item: IDX Insights.IX_InsightsDeployment_Index CLUSTERED 0
Missing item: IDX Insights.IX_InsightsDeployment_Index CompletedTime
Missing item: IDX Insights.IX_InsightsDeployment_Index EnvironmentId
Missing item: IDX Insights.IX_InsightsDeployment_Index ProjectId
Missing item: IDX Insights.IX_InsightsDeployment_Index SpaceId
Missing item: IDX Insights.IX_InsightsRelease_Index CLUSTERED 0
Missing item: IDX Insights.IX_InsightsRelease_Index ProjectId
Missing item: IDX Insights.IX_InsightsRelease_Index SpaceId 

Thanks!

Hey @Wildpipe,

Sorry all of those indexes were not cleared up, that is a lot less than you had before though, those indexes come from a newish DB table which has a slightly different naming convention than our others so the script I was running was missing them out. I have changed the script and here is the code to clear those missing indexes up!

CREATE CLUSTERED INDEX IX_InsightsDeployment_Index ON Insights.Deployment (  CompletedTime ASC  , SpaceId ASC  , ProjectId ASC  , EnvironmentId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
CREATE CLUSTERED INDEX IX_InsightsRelease_Index ON Insights.Release (  SpaceId ASC  , ProjectId ASC  )   WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ]

Let me know if that allows the check to pass,
Kind Regards,
Clare

Applying those last couple of queries to the database made the diagnostics check pass.
Thanks for all the help!

Regards,
Karl

Hey Karl,

You’re very welcome! Thanks for letting us know you’re in a good state.

We’re always here if you run into any snags in the future.

Best,
Jeremy

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.