System Integrity failure: Missing Indexes

Octopus Server
1

When running the System Integrity check we get the following errors. We have recently upgraded the server and now have these errors. I’ve seen other posts where Octo support has provided customized scripts to run for this same issue. I guess I’m here to get my script to run.

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_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_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_User_DataVersion DataVersion

Missing item: IDX dbo.IX_User_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
2

Hi Clint,

Thanks for reaching out, and sorry to hear that you have some missing indexes in your Schema we saw this with older versions being booted up against an upgraded database, which would delete the unexpected indexes - does this sound plausible for what could have happened here? This functionality has changed in recent versions of Octopus to warn instead of deleting, so this avenue of schema corruption has been removed.

Can you let us know what version of Octopus you’re currently running - from here, we can get you the correct indexes to resolve this.

3

Hello Justin,

the version we are running is 2022.3.10780.

4

Thanks Clint - this should get those indexes recreated for you:

 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 ] 
 GO
 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 ] 
 GO
 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 ] 
 GO
 CREATE CLUSTERED INDEX IX_HalibutMessageQueueItem_SequenceNumber ON dbo.HalibutMessageQueueItem (  Endpoint ASC  , Direction ASC  , 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 ] 
 GO
 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 ] 
 GO
 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 ] 
 GO
 CREATE NONCLUSTERED INDEX IX_TaskLogEntry_CorrelationId_OccurredAt ON dbo.TaskLogEntry (  CorrelationId ASC  , OccurredAt 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 ] 
 GO
 CREATE CLUSTERED INDEX IX_TaskLogEntry_SequenceNumber ON dbo.TaskLogEntry (  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 ] 
 GO
 CREATE  UNIQUE NONCLUSTERED INDEX IX_TaskLogRelationship_CorrelationId ON dbo.TaskLogRelationship (  CorrelationId 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 ] 
 GO
 CREATE CLUSTERED INDEX IX_TaskLogRelationship_SequenceNumber ON dbo.TaskLogRelationship (  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 ] 
 GO
 CREATE NONCLUSTERED INDEX IX_TaskLogRelationships_ParentCorrelationId ON dbo.TaskLogRelationship (  ParentCorrelationId 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 ] 
 GO
 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 ] 
 GO
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 ] 
 GO
 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 ] 
 GO
5

That worked. The Integrity check came back successful. Thank you.

6

Most welcome, Clint - have a great weekend.

7

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