Check System Integrity - Failing

[zachickster (Brian Chick)]

Hello -

After patching our Octopus server and tracking down unrelated deployment issues I noticed that our Check System Integrity tasks were queuing up and failing. Looking at the log reveled the following schema issues:

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_NotAfter NONCLUSTERED 0
Missing item: IDX dbo.IX_Certificate_NotAfter NotAfter
Missing item: IDX dbo.IX_Certificate_Thumbprint NONCLUSTERED 0
Missing item: IDX dbo.IX_Certificate_Thumbprint Thumbprint
Missing item: IDX dbo.IX_DeploymentRelatedMachine_Machine MachineId
Missing item: IDX dbo.IX_DeploymentRelatedMachine_Machine NONCLUSTERED 0
Missing item: IDX dbo.IX_Event_CommonSearch Category
Missing item: IDX dbo.IX_Event_CommonSearch EnvironmentId
Missing item: IDX dbo.IX_Event_CommonSearch Id
Missing item: IDX dbo.IX_Event_CommonSearch NONCLUSTERED 0
Missing item: IDX dbo.IX_Event_CommonSearch Occurred
Missing item: IDX dbo.IX_Event_CommonSearch ProjectId
Missing item: IDX dbo.IX_Event_CommonSearch RelatedDocumentIds
Missing item: IDX dbo.IX_Event_CommonSearch TenantId
Missing item: IDX dbo.IX_Event_CommonSearch UserId
Missing item: IDX dbo.IX_Event_Occurred Id
Missing item: IDX dbo.IX_Event_Occurred NONCLUSTERED 0
Missing item: IDX dbo.IX_Event_Occurred Occurred
Missing item: IDX dbo.IX_ProjectTrigger_Project NONCLUSTERED 0
Missing item: IDX dbo.IX_ProjectTrigger_Project ProjectId
Missing item: IDX dbo.IX_TenantVariable_TenantId NONCLUSTERED 0
Missing item: IDX dbo.IX_TenantVariable_TenantId TenantId
Missing item: IDX dbo.IX_User_EmailAddress EmailAddress
Missing item: IDX dbo.IX_User_EmailAddress NONCLUSTERED 0
Missing item: IDX dbo.IX_User_ExternalId ExternalId
Missing item: IDX dbo.IX_User_ExternalId NONCLUSTERED 0

Some months back we upgraded from 2018.6.8 to 2018.10.2 LTS. The upgrade appeared to go smoothly. Is it possible that I missed something during that upgrade process? Would upgrading to 2019.3.1 LTS resolve the issue?

Thanks,
Brian Chick

[Alex.Rolley]

Hi Brian,

Thanks for your patience here!

Looking at that list it appears that you are missing a bunch of indexes on your Octopus database. I had a look and those indexes are applied across a variety of our upgrade scripts, at this point I don’t have a concrete theory on why they would be missing from your database.

Either way, they should be re-applied with your next upgrade and won’t cause any particular issues with your Octopus instance, apart from potential performance reductions. If you haven’t noticed any performance impact to this point then I would expect you to be OK going forward.

If the indexes are still missing post upgrade please let me know and I can assist with the SQL required to re-apply them.

If there is anything else I can assist with please let me know,

Regards,
Alex