Missing Indexes

Please advise on what we should do to create all of the missing indexes from the Diagnostic System Check. Every time we update our Octopus Server we run into this same issue and have to recreate these indexes. Previously the script could be found in The INSERT statement conflicted with the FOREIGN KEY constraint but the file is no longer available.

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

Good morning @Aaron.Morse,

Thank you for contacting Octopus Support and sorry to hear you are seeing missing indexes in your Integrity check.

Am I able to get your current Octopus Server version please (not the one you are trying to upgrade to but the one you are currently using) I need that in order to generate the script for the missing indexes.

As a side note, the missing index scripts are instance version specific. If you use the same script after you upgrade a few times that script might affect your current instance as we do sometimes change the database indexes.

If you run into any missing index issues please email us or pop the missing indexes on our forums and let us know what Octopus version you are currently on and we will get you the script to run on that version.

Once I get your current Octopus Server version I can get to generateing you the script to fix that up.

Kind Regards,
Clare

Thanks for the reply. Our current version is v2022.1 (Build 2663)

Hey @Aaron.Morse,

Great thanks, its building the script now, it might take 10 minutes or so but I will post up the results once I have them.

Kind Regards,
Clare

1 Like

Hey @Aaron.Morse,

As promised here is the script for the missing indexes. To be safe please run a database backup before running the script.

Also, as mentioned, this script is bespoke to your current server version and missing indexes so if any customers see this post please get us to create your own bespoke script do not run this one.

Once you have run the script run the integrity check again and ensure it passes, if it does not pass post up the other missing indexes and I will generate a new script for those.

Kind Regards,
Clare

2022.1.2663IndexFixes.txt (4.0 KB)

Thank you Clare, I have run the scripts after making a db backup and the System Integrity Checks are passing again.
Much appreciated

Hey @Aaron.Morse,

Great news thanks for letting me know! Hopefully you can now upgrade without issues!

Reach out in future if there is anything else we can assist with as we are always on hand to help!

Kind Regards and Happy Deployments,
Clare

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