System Integrity Check Failure

Octopus Server
, , ,
1

After upgrading to version 2022.3.10530 the system integrity check is failing on the schema check:

                | Failed: Check System Integrity

01:32:03 Error | Schema… [ Failed ]
01:32:03 Info | Release channel belongs to the appropriate project… [ Passed ]
01:32:03 Info | Orphaned tenanted deployments… [ Passed ]
01:32:03 Info | Orphaned channels… [ Passed ]
01:32:03 Info | Orphaned releases… [ Passed ]
01:32:03 Info | Only one unfrozen deployment process per project… [ Passed ]
01:32:03 Fatal | One or more data errors were found.
|
| Failed: Schema
01:32:03 Error | 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_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

3

Good afternoon @bradford.walton,

Thank you for contacting Octopus Support, welcome to the forums! I am sorry to hear you are experiencing indexing issues since upgrading. Thank you for providing us with the index failures.

I have attached a text file with an SQL query you can run in SQL server manager to get those indexes created for you. Please make sure you take a database backup before running the SQL query just in case something goes wrong.

FixedIndexes.txt (3.4 KB)

Once you run the query if you re-run the integrity check for us and make sure it then passes.

I look forward to hearing from you,

Kind Regards,

Clare

4

Thanks Clare, we ran the script but seem to still be missing something.

Failed: Schema
15:16:40 Error | Unexpected item: IDX dbo.IX_InsightsReport_SpaceId NONCLUSTERED 0
| Unexpected item: IDX dbo.IX_TaskLogEntry_SequenceNumber NONCLUSTERED 0
| Unexpected item: IDX dbo.IX_TaskLogRelationship_SequenceNumber NONCLUSTERED 0
| Missing item: IDX dbo.IX_InsightsReport_SpaceId CLUSTERED 0
| Missing item: IDX dbo.IX_TaskLogEntry_SequenceNumber CLUSTERED 0
| Missing item: IDX dbo.IX_TaskLogRelationship_SequenceNumber CLUSTERED 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
|

6

Hi @bradford.walton,

Thanks for getting back! I’m sorry for the delay in responding here but I was breaking the indexes in my local instance to make sure the following fix actually did fix this for you.

The Insights indexes are new, as they relate to the feature by the same name. I also noticed that a few of your indexes have been added as nonclustered, rather than clustered. So if you could take a fresh backup of your database and try the following steps, Octopus should be happy once more.

  1. delete the following indexes which were created as nonclustered
dbo.IX_InsightsReport_SpaceId
dbo.IX_TaskLogEntry_SequenceNumber
dbo.IX_TaskLogRelationship_SequenceNumber
  1. Run the following code to re-create them as clustered indexes along with a few which are still listed as missing.
CREATE CLUSTERED INDEX [IX_InsightsRelease_Index] on [dbo].[Insights.Release](SpaceId, ProjectId)
CREATE CLUSTERED INDEX [IX_InsightsDeployment_Index] on [dbo].[Insights.Deployment](CompletedTime, SpaceId, ProjectId, EnvironmentId)
CREATE CLUSTERED INDEX [IX_InsightsReport_SpaceId] ON [dbo].[InsightsReport]([SpaceId])
CREATE CLUSTERED INDEX [IX_TaskLogEntry_SequenceNumber] ON [dbo].[TaskLogEntry]([SequenceNumber])
CREATE CLUSTERED INDEX [IX_TaskLogRelationship_SequenceNumber] ON [dbo].[TaskLogRelationship]([SequenceNumber])
  1. Perform System Integrity Check in Octopus

I have tested these on my instance, but the circumstances which caused yours to break are different to my manually deleting them while testing. Everything looks to be running fine on my end after adding them back though.

Let me know if you have any issues or questions about this.

Best regards,
Daniel

7

The errors are getting shorter and shorter.

Unexpected item: IDX dbo.IX_InsightsReport_SpaceID CLUSTERED 0
Unexpected item: IDX dbo.IX_InsightsReport_SpaceID SpaceId
Missing item: IDX dbo.IX_InsightsReport_SpaceId CLUSTERED 0
Missing item: IDX dbo.IX_InsightsReport_SpaceId SpaceId

8

Hi @bradford.walton,

Thanks for getting back. This error is most likely due to case sensitivity. dbo.IX_InsightsReport_SpaceID is unexpected because the database is expecting dbo.IX_InsightsReport_SpaceId. The difference being SpaceID vs. SpaceId.

To fix this, you will need to remove the unexpected index shown and re-add it, paying close attention to the expected capitalisation of the sting.

Let me know if you’re still stuck here.

Best regards,
Daniel

9

Thanks for the help, everything is back to normal now.

10

Hi @bradford.walton,

No worries at all, we’re always happy to help. Just keep in mind that the Octopus database is VERY picky about the formatting and case sensitivity of its data. It gets upset when things aren’t exactly as it expects them to be.

Feel free to get in touch at any time.

Best regards,
Daniel

11

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