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.
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.
delete the following indexes which were created as nonclustered
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])
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.
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.
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.