System Integrity Check Failure

Legacy Execution
1

Hi,

Over the last couple of days we have noticed the Check System Integrity task has been failing. The raw log is below;-

Task ID: ServerTasks-65037
Task status: Failed
Task queued: 08 January 2019 09:52:42
Task started: 08 January 2019 09:52:42
Task completed: 08 January 2019 09:52:43
Task duration: 1 second
Server version: 2018.9.16+Branch.master.Sha.ddd39553d927caf52eeae6699dc605ae374e2e12
Server node: [redacted]

                | Failed: Check System Integrity

09:52:43 Info | Unexpected related document id format… [ Passed ]
09:52:43 Error | Schema… [ Failed ]
09:52:43 Info | Release channel belongs to the appropriate project… [ Passed ]
09:52:43 Info | Orphaned tenanted deployments… [ Passed ]
09:52:43 Info | Orphaned channels… [ Passed ]
09:52:43 Info | Orphaned releases… [ Passed ]
09:52:43 Info | Only one unfrozen deployment process per project… [ Passed ]
09:52:43 Fatal | One or more data errors were found.
|
| Failed: Schema
09:52:43 Error | Unexpected item: IDX dbo.IX_ProjectTrigger_Project Name
| Missing item: IDX dbo.IX_Artifact_TenantId NONCLUSTERED 0
| Missing item: IDX dbo.IX_Artifact_TenantId TenantId
| Missing item: IDX dbo.IX_Certificate_Created Created
| Missing item: IDX dbo.IX_Certificate_Created NONCLUSTERED 0
| Missing item: IDX dbo.IX_Certificate_Thumbprint NONCLUSTERED 0
| Missing item: IDX dbo.IX_Certificate_Thumbprint Thumbprint
| Missing item: IDX dbo.IX_Event_AutoId AutoId
| Missing item: IDX dbo.IX_Event_AutoId NONCLUSTERED 0
| Missing item: IDX dbo.IX_Machine_MachinePolicy MachinePolicyId
| Missing item: IDX dbo.IX_Machine_MachinePolicy NONCLUSTERED 0
| Missing item: IDX dbo.IX_TenantVariable_TenantId NONCLUSTERED 0
| Missing item: IDX dbo.IX_TenantVariable_TenantId TenantId
| Missing item: IDX dbo.IX_User_DisplayName DisplayName
| Missing item: IDX dbo.IX_User_DisplayName NONCLUSTERED 0
| Missing item: IDX dbo.IX_User_EmailAddress EmailAddress
| Missing item: IDX dbo.IX_User_EmailAddress NONCLUSTERED 0
| Missing item: IDX dbo.UQ_ProjectTriggerNameUnique Name
| Missing item: IDX dbo.UQ_ProjectTriggerNameUnique NONCLUSTERED 1
| Missing item: IDX dbo.UQ_ProjectTriggerNameUnique ProjectId
|

We are currently running with version v2018.9.16 and I am not aware of any changes since we upgraded. Any ideas what is causing this, if we should be concerned and if there is any resolution?

Many thanks

3

Hi Daniel,

I already have responded to your email, however I thought I should post the response here as well in case this can help someone else in future.

​It seems that somewhere along the way that some indexes have been removed from your Octopus database, which is what is causing the vast majority of the errors in your System Integrity check logs. To resolve these errors you will need to re-add the indexes (and a table constraint) which you can do by running the following T-SQL against your Octopus database.
​
​WARNING: Please make sure you have a backup of your Database and Master Key before attempting any changes.
​

​CREATE INDEX IX_Certificate_Created 
​    ON dbo.[Certificate] ([Created])
​
​CREATE INDEX IX_Artifact_TenantId
    ON dbo.[Artifact] ([TenantId])
​
​CREATE INDEX IX_Certificate_Thumbprint 
​    ON dbo.[Certificate] ([Thumbprint])
​
​CREATE INDEX IX_Machine_MachinePolicy
    ON dbo.[Machine] ([MachinePolicyId])

​CREATE INDEX IX_TenantVariable_TenantId 
​    ON dbo.[TenantVariable] ([TenantId])

​CREATE INDEX IX_User_DisplayName 
​    ON dbo.[User] ([DisplayName])

​CREATE INDEX IX_User_EmailAddress 
​    ON dbo.[User] ([EmailAddress])

​ALTER TABLE [ProjectTrigger] 
    ADD CONSTRAINT [UQ_ProjectTriggerNameUnique] UNIQUE([ProjectId],[Name])

​
​The other warning is that you have an index created that is not a standard Octopus index:
​
​Unexpected item: IDX dbo.IX_ProjectTrigger_Project Name

​This may have been applied for a reason, and apart from showing an error in your System Integrity check won’t cause any issues with your Octopus instance. This may be removed automatically during an upgrade, or if you decide it’s not need can also be removed.
​
​Please let me know if there is anything else that I can assist with,
​
​Regards,

4

Thanks! I’ve asked our DBA team to check the indexing so hopefully the check will pass today.

5

No problems, let me know if there is anything else you need!

Regards,
Alex

6

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