ProjectModifyResponder SQL Error

[Naman.Kumar]

Facing the issue after upgrade.
Current Version: 2021.1.7738

System.Exception: Error while executing SQL command in transaction 'ModifyResponder ProjectModifyResponder': Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.
The command being executed was:
DELETE FROM [dbo].[EventRelatedDocument]
WHERE ([EventId] = @eventid)
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.

Previous thread I found with no resolution: ModifyResponder error post Octopus Server upgrade

[jeremy.miller]

Hi @Naman.Kumar,

Thanks for reaching out and welcome to the forums!

1. Can we please do a System integrity check and make sure there are no errors?

2. If there are no errors there, 2021.1 actually increased the memory usage that Octopus requires on your SQL Server. Could we please check your SQL Server Metrics to make sure that we have a lot of capacity left there?

3. It might also be a good idea to check your SQL Fragmentation. There is a Community Step Template called SQL Server Fragmentation Query that if you run it, it will give you the fragmentation. We can take a look at that and see if it’s high or not.

Please let me know how it goes.

Best,
Jeremy

[Naman.Kumar]

                    | Failed: Check System Integrity
15:05:11   Info     |   Unexpected related document id format....................... [ Passed ]
15:05:11   Error    |   Schema...................................................... [ Failed ]
15:05:11   Info     |   Release channel belongs to the appropriate project.......... [ Passed ]
15:05:11   Info     |   Orphaned tenanted deployments............................... [ Passed ]
15:05:11   Info     |   Orphaned channels........................................... [ Passed ]
15:05:11   Info     |   Orphaned releases........................................... [ Passed ]
15:05:11   Info     |   Only one unfrozen deployment process per project............ [ Passed ]
15:05:11   Fatal    |   One or more data errors were found.
                    | 
                    |   Failed: Schema
15:05:11   Error    |     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_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_Subscription_DataVersion DataVersion
                    |     Missing item: IDX dbo.IX_Subscription_DataVersion NONCLUSTERED 0

Found this in System Integrity Check.

I believe the version upgrade activity didn’t update the schema on the database. Do we have a known resolution to this?

[jeremy.miller]

Hi @Naman.Kumar,

This is actually a known issue and we are working on the root cause fix, but for now, we need to re-add the indexes manually via sql.

If you can give me a moment I will get the correct indexes for you.

Best,
Jeremy

[jeremy.miller]

Hi @Naman.Kumar,

Thanks for being patient.

Please run the following queries to recreate the missing indexes. Please take a backup of your SQL DB before you run these queries. We don’t typically like to modify the SQL DB manually as there can be unintended consequences so we only do it when absolutely necessary. Once you’ve created the Indexes, please run the system integrity check and let me know if it’s working.

CREATE INDEX IX_DeploymentSettings_DataVersion ON DeploymentSettings(DataVersion)

CREATE UNIQUE CLUSTERED INDEX IX_DeploymentSettings_SpaceId_ProjectId ON DeploymentSettings(ProjectId, SpaceId)

CREATE INDEX IX_EventRelatedDocument_RelatedDocumentIdPrefix ON EventRelatedDocument(RelatedDocumentIdPrefix, RelatedDocumentId, EventId)

CREATE INDEX IX_EventRelatedDocument_EventId_RelatedDocumentIdPrefix ON EventRelatedDocument(EventId, RelatedDocumentIdPrefix, RelatedDocumentId)

CREATE INDEX IX_Release_SpaceId_ProjectId_ChannelId_Assembled ON Release(SpaceId, ProjectId, ChannelId, Assembled, Id, Version, ProjectVariableSetSnapshotId, ProjectDeploymentProcessSnapshotId)

CREATE INDEX IX_Subscription_DataVersion ON Subscription(DataVersion)

Best,
Jeremy