Missing database indexes

Octopus Server
, , ,
1

Hi,

The “Apply retention policies” job on our Octopus Deploy has been failing with a “Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.” for the last couple of days. I finally had some time to troubleshoot it and found that there suddenly seem to be a lot of indexes missing from a lot different tables. The diagnostic check gives the following error:

Missing item: IDX dbo.IX_BuildInformationCreatedDate CreatedDate
Missing item: IDX dbo.IX_BuildInformationCreatedDate NONCLUSTERED 0
Missing item: IDX dbo.IX_BuildInformationPackageIdPerSpace NONCLUSTERED 0
Missing item: IDX dbo.IX_BuildInformationPackageIdPerSpace PackageId
Missing item: IDX dbo.IX_BuildInformationPackageIdPerSpace SpaceId
Missing item: IDX dbo.IX_DeploymentSettings_DataVersion DataVersion
Missing item: IDX dbo.IX_DeploymentSettings_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_DeploymentSettings_SpaceId_ProjectId CLUSTERED 0
Missing item: IDX dbo.IX_DeploymentSettings_SpaceId_ProjectId ProjectId
Missing item: IDX dbo.IX_DeploymentSettings_SpaceId_ProjectId SpaceId
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_ProjectTrigger_RunbookId NONCLUSTERED 0
Missing item: IDX dbo.IX_ProjectTrigger_RunbookId RunbookId
Missing item: IDX dbo.IX_ProjectTrigger_RunbookId SpaceId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled Assembled
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled ChannelId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled Id
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled NONCLUSTERED 0
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled ProjectDeploymentProcessSnapshotId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled ProjectId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled ProjectVariableSetSnapshotId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled SpaceId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled Version
Missing item: IDX dbo.IX_Runbook_DataVersion DataVersion
Missing item: IDX dbo.IX_Runbook_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Runbook_DataVersion SpaceId
Missing item: IDX dbo.IX_Runbook_ProjectId NONCLUSTERED 0
Missing item: IDX dbo.IX_Runbook_ProjectId ProjectId
Missing item: IDX dbo.IX_Runbook_Published_PublishedRunbookSnapshotId NONCLUSTERED 0
Missing item: IDX dbo.IX_Runbook_Published_PublishedRunbookSnapshotId PublishedRunbookSnapshotId
Missing item: IDX dbo.IX_Runbook_Published_PublishedRunbookSnapshotId SpaceId
Missing item: IDX dbo.IX_Runbook_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Runbook_SpaceId SpaceId
Missing item: IDX dbo.IX_RunbookProcess_ProjectId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookProcess_ProjectId ProjectId
Missing item: IDX dbo.IX_RunbookProcess_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookProcess_SpaceId SpaceId
Missing item: IDX dbo.IX_RunbookRun_Index Created
Missing item: IDX dbo.IX_RunbookRun_Index EnvironmentId
Missing item: IDX dbo.IX_RunbookRun_Index Id
Missing item: IDX dbo.IX_RunbookRun_Index Name
Missing item: IDX dbo.IX_RunbookRun_Index NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_Index ProjectId
Missing item: IDX dbo.IX_RunbookRun_Index RunbookSnapshotId
Missing item: IDX dbo.IX_RunbookRun_Index TaskId
Missing item: IDX dbo.IX_RunbookRun_ProjectId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_ProjectId ProjectId
Missing item: IDX dbo.IX_RunbookRun_RunbookId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_RunbookId RunbookId
Missing item: IDX dbo.IX_RunbookRun_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_SpaceId SpaceId
Missing item: IDX dbo.IX_RunbookRun_TenantId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_TenantId TenantId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory Created
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory DeployedBy
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory EnvironmentId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory Name
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory ProjectId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory RunbookId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory RunbookSnapshotId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory TaskId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory TenantId
Missing item: IDX dbo.IX_RunbookRunHistory_IsPublished IsPublished
Missing item: IDX dbo.IX_RunbookRunHistory_IsPublished NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_Assembled Assembled
Missing item: IDX dbo.IX_RunbookSnapshot_Assembled NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_DataVersion DataVersion
Missing item: IDX dbo.IX_RunbookSnapshot_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_FrozenRunbookProcessId FrozenRunbookProcessId
Missing item: IDX dbo.IX_RunbookSnapshot_FrozenRunbookProcessId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_ProjectId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_ProjectId ProjectId
Missing item: IDX dbo.IX_RunbookSnapshot_RunbookId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_RunbookId RunbookId
Missing item: IDX dbo.IX_RunbookSnapshot_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_SpaceId SpaceId
Missing item: IDX dbo.IX_ScopedUserRole_SpaceDataVersion DataVersion
Missing item: IDX dbo.IX_ScopedUserRole_SpaceDataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_ScopedUserRole_SpaceDataVersion SpaceId
Missing item: IDX dbo.IX_Space_DataVersion DataVersion
Missing item: IDX dbo.IX_Space_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Subscription_DataVersion DataVersion
Missing item: IDX dbo.IX_Subscription_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Team_SpaceDataVersion DataVersion
Missing item: IDX dbo.IX_Team_SpaceDataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Team_SpaceDataVersion SpaceId
Missing item: IDX dbo.IX_Tenant_ClonedFromTenantId ClonedFromTenantId
Missing item: IDX dbo.IX_Tenant_ClonedFromTenantId NONCLUSTERED 0
Missing item: IDX dbo.IX_UserRole_DataVersion DataVersion
Missing item: IDX dbo.IX_UserRole_DataVersion NONCLUSTERED 0

Is there any recommendation on how to resolve this issue? How can indexes suddenly dissapear? If it is of any importance we are running version 2021.2.7713 and have been on this version since some time before christmas.

Thanks!

EDIT: After having looked through the logs on the Octopus Deploy server I believe these indexes most likely disappeared in conjunction with our last update of Octopus Deploy.

4

Hey Karl,

Thanks for reaching out. Sorry you’ve run into this.

To fix this we will need to recreate the indexes in SQL and re-run the integrity check.

Here is the query to create the indexes for your missing ones:

CREATE INDEX IX_BuildInformationCreatedDate ON BuildInformation(CreatedDate)
CREATE INDEX IX_BuildInformationPackageIdPerSpace ON BuildInformation(SpaceId, PackageId)
CREATE INDEX IX_DeploymentSettings_DataVersion ON DeploymentSettings(DataVersion)			
CREATE UNIQUE CLUSTERED INDEX IX_DeploymentSettings_SpaceId_ProjectId ON DeploymentSettings(ProjectId, SpaceId)
CREATE INDEX IX_EventRelatedDocument_EventId_RelatedDocumentIdPrefix ON EventRelatedDocument(EventId, RelatedDocumentIdPrefix, RelatedDocumentId)
CREATE INDEX IX_EventRelatedDocument_RelatedDocumentIdPrefix ON EventRelatedDocument(RelatedDocumentIdPrefix, RelatedDocumentId, EventId)
CREATE INDEX IX_ProjectTrigger_RunbookId ON ProjectTrigger(SpaceId, RunbookId)
CREATE INDEX IX_Release_SpaceId_ProjectId_ChannelId_Assembled ON Release(SpaceId, ProjectId, ChannelId, Assembled, Id, Version, ProjectVariableSetSnapshotId, ProjectDeploymentProcessSnapshotId)
CREATE INDEX IX_Runbook_DataVersion ON Runbook(SpaceId, DataVersion)
CREATE INDEX IX_Runbook_ProjectId ON Runbook(ProjectId)
CREATE INDEX IX_Runbook_Published_PublishedRunbookSnapshotId ON Runbook(SpaceId, PublishedRunbookSnapshotId)
CREATE INDEX IX_Runbook_SpaceId ON Runbook(SpaceId)
CREATE INDEX IX_RunbookProcess_ProjectId ON RunbookProcess(ProjectId)
CREATE INDEX IX_RunbookProcess_SpaceId ON RunbookProcess(SpaceId)
CREATE INDEX IX_RunbookRun_Index ON RunbookRun(RunbookSnapshotId, TaskId, EnvironmentId, Id, ProjectId, Name, Created)
CREATE INDEX IX_RunbookRun_ProjectId ON RunbookRun(ProjectId)
CREATE INDEX IX_RunbookRun_RunbookId ON RunbookRun(RunbookId)
CREATE INDEX IX_RunbookRun_SpaceId ON RunbookRun(SpaceId)
CREATE INDEX IX_RunbookRun_TenantId ON RunbookRun(TenantId)
CREATE INDEX IX_RunbookRun_UpdateRunbookRunHistory ON RunbookRun(TaskId, Name, EnvironmentId, ProjectId, RunbookId, RunbookSnapshotId, DeployedBy, TenantId, Created)
CREATE INDEX IX_RunbookRunHistory_IsPublished ON RunbookRunHistory(IsPublished)
CREATE INDEX IX_RunbookSnapshot_Assembled ON RunbookSnapshot(Assembled)
CREATE INDEX IX_RunbookSnapshot_DataVersion ON RunbookSnapshot(DataVersion)
CREATE INDEX IX_RunbookSnapshot_FrozenRunbookProcessId ON RunbookSnapshot(FrozenRunbookProcessId)
CREATE INDEX IX_RunbookSnapshot_ProjectId ON RunbookSnapshot(ProjectId)
CREATE INDEX IX_RunbookSnapshot_RunbookId ON RunbookSnapshot(RunbookId)
CREATE INDEX IX_RunbookSnapshot_SpaceId ON RunbookSnapshot(SpaceId)
CREATE INDEX IX_Subscription_DataVersion ON Subscription(DataVersion)
CREATE INDEX IX_Tenant_ClonedFromTenantId ON Tenant(ClonedFromTenantId)
CREATE INDEX IX_ScopedUserRole_SpaceDataVersion ON ScopedUserRole(SpaceId, DataVersion)
CREATE INDEX IX_Space_DataVersion ON Space(DataVersion)
CREATE INDEX IX_Team_SpaceDataVersion ON Team(SpaceId, DataVersion)
CREATE INDEX IX_UserRole_DataVersion ON UserRole(DataVersion)

Can you please let me know if that gets it fixed up?

Which version did you upgrade from? We’ve found that these indexes get removed if you’re using an older version of Octopus.Server.exe to run commands. Is there some automation running somewhere or someone is using one to run commands from?

Please let me know!

Best,
Jeremy

5

Thanks for the help! Adding those indexes made it possible to run the retention jobs again.

We updated from 2021.1.7316 to 2021.2.7713. The installation has been running for at least a couple of years with in place updates. No automation or anything like that.

6

Hey Karl,

You’re very welcome! Thanks for letting me know you’re in a good working state again.

If you get time, can you please give me a quick rundown on your upgrade process so that I can pass it along to the engineers?

Best,
Jeremy

7

The upgrade process is very simple:

  1. Download the version to upgrade to from Download Octopus Server 2021.3 | Try free for up to 10 targets - Octopus Deploy
  2. Put Octopus Deploy in Maintenance mode
  3. Double click the installation file on the Octopus Deploy server
  4. Follow the installation wizard
  5. Wait for installation to finish
  6. Disable Maintenance mode
  7. Done
1 Like
8

Hey Karl,

Thanks so much for the steps for us to try and reproduce.

Please let us know if you run into any other issues.

I hope you have a great rest of your week.

Best,
Jeremy

9

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