"Execution Timeout Expired" during approval step in v2022.1 (Build 2342)

[rjunruh]

After upgrading from v2021.3 (Build 10526) (a custom build for another error) to v2022.1 (Build 2342) we are getting this error during an approval step:

The step failed: Activity UCSB - Approval to Create App on the Octopus Server failed with error 'Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Operation cancelled by user.
The statement has been terminated.
Current transactions: 

Transaction 'DeploymentTaskController|ServerTasks-181702-af0ca2f8-5eaa-499e-baf7-5797ac560eb8|T62' Open with 14 commands started at 2022-04-12T16:42:18 (60.24 seconds ago)
2022-04-12T16:42:18 SELECT TOP 1 Id,Name,Description,QueueTime,StartTime,CompletedTime,ErrorMessage,ConcurrencyTag,State,HasPendingInterruptions,HasWarningsOrErrors,ServerNodeId,ProjectId,EnvironmentId,TenantId,DurationSeconds,DataVersion,SpaceId,RunbookId,LastModified,BusinessProcessState,ServerTaskType,JSON FROM [dbo].[ServerTask] WHERE [Id] = @Id
2022-04-12T16:42:18 SELECT TOP 1 Id,Name,Description,QueueTime,StartTime,CompletedTime,ErrorMessage,ConcurrencyTag,State,HasPendingInterruptions,HasWarningsOrErrors,ServerNodeId,ProjectId,EnvironmentId,TenantId,DurationSeconds,DataVersion,SpaceId,RunbookId,LastModified,BusinessProcessState,ServerTaskType,JSON FROM [dbo].[ServerTask] WHERE [Id] = @Id
2022-04-12T16:42:18 SELECT TOP 1 Id,Name,Description,QueueTime,StartTime,CompletedTime,ErrorMessage,ConcurrencyTag,State,HasPendingInterruptions,HasWarningsOrErrors,ServerNodeId,ProjectId,EnvironmentId,TenantId,DurationSeconds,DataVersion,SpaceId,RunbookId,LastModified,BusinessProcessState,ServerTaskType,JSON FROM [dbo].[ServerTask] WHERE [Id] = @Id
2022-04-12T16:42:18 UPDATE [dbo].[ServerTask]  SET [Name] = @Name, [Description] = @Description, [QueueTime] = @QueueTime, [StartTime] = @StartTime, [CompletedTime] = @CompletedTime, [ErrorMessage] = @ErrorMessage, [ConcurrencyTag] = @ConcurrencyTag, [State] = @State, [HasPendingInterruptions] = @HasPendingInterruptions, [HasWarningsOrErrors] = @HasWarningsOrErrors, [ServerNodeId] = @ServerNodeId, [ProjectId] = @ProjectId, [EnvironmentId] = @EnvironmentId, [TenantId] = @TenantId, [RunbookId] = @RunbookId, [DurationSeconds] = @DurationSeconds, [SpaceId] = @SpaceId, [LastModified] = @LastModified, [BusinessProcessState] = @BusinessProcessState, [ServerTaskType] = @ServerTaskType, [JSON] = @JSON WHERE [Id] = @Id
2022-04-12T16:42:18 INSERT INTO [dbo].[Event]  ([Id], [RelatedDocumentIds], [ProjectId], [EnvironmentId], [TenantId], [Category], [UserId], [Username], [Occurred], [UserAgent], [Message], [SpaceId], [ChangeDetails], [JSONBlob], [JSON])  VALUES 
(@Id, @RelatedDocumentIds, @ProjectId, @EnvironmentId, @TenantId, @Category, @UserId, @Username, @Occurred, @UserAgent, @Message, @SpaceId, @ChangeDetails, @JSONBlob, @JSON)

… plus more SQL

I see missing indices in a system integrity check but these same indices are showing up in our working, prod server which has not been updated:

Unexpected item: USER_TABLE dbo.Script0202EnsureArtifactScopesAreConsistent.Created datetimeoffset(10/34/7) 0
Unexpected item: USER_TABLE dbo.Script0202EnsureArtifactScopesAreConsistent.DataVersion timestamp(8/0/0) 0
Unexpected item: USER_TABLE dbo.Script0202EnsureArtifactScopesAreConsistent.EnvironmentId nvarchar(100/0/0) 1
Unexpected item: USER_TABLE dbo.Script0202EnsureArtifactScopesAreConsistent.Filename nvarchar(400/0/0) 0
Unexpected item: USER_TABLE dbo.Script0202EnsureArtifactScopesAreConsistent.Id nvarchar(100/0/0) 0
Unexpected item: USER_TABLE dbo.Script0202EnsureArtifactScopesAreConsistent.JSON nvarchar(-1/0/0) 0
Unexpected item: USER_TABLE dbo.Script0202EnsureArtifactScopesAreConsistent.ProjectId nvarchar(100/0/0) 1
Unexpected item: USER_TABLE dbo.Script0202EnsureArtifactScopesAreConsistent.RelatedDocumentIds nvarchar(-1/0/0) 1
Unexpected item: USER_TABLE dbo.Script0202EnsureArtifactScopesAreConsistent.ServerTaskId nvarchar(100/0/0) 0
Unexpected item: USER_TABLE dbo.Script0202EnsureArtifactScopesAreConsistent.SpaceId nvarchar(100/0/0) 0
Unexpected item: USER_TABLE dbo.Script0202EnsureArtifactScopesAreConsistent.TenantId nvarchar(100/0/0) 1
Unexpected item: VIEW dbo.ServerTaskLocalTime.CompletedTime datetime2(8/27/7) 1
Unexpected item: VIEW dbo.ServerTaskLocalTime.ConcurrencyTag nvarchar(200/0/0) 1
Unexpected item: VIEW dbo.ServerTaskLocalTime.DataVersion timestamp(8/0/0) 0
Unexpected item: VIEW dbo.ServerTaskLocalTime.Description nvarchar(-1/0/0) 0
Unexpected item: VIEW dbo.ServerTaskLocalTime.DurationSeconds int(4/10/0) 0
Unexpected item: VIEW dbo.ServerTaskLocalTime.EnvironmentId nvarchar(100/0/0) 1
Unexpected item: VIEW dbo.ServerTaskLocalTime.ErrorMessage nvarchar(-1/0/0) 1
Unexpected item: VIEW dbo.ServerTaskLocalTime.HasPendingInterruptions bit(1/1/0) 0
Unexpected item: VIEW dbo.ServerTaskLocalTime.HasWarningsOrErrors bit(1/1/0) 0
Unexpected item: VIEW dbo.ServerTaskLocalTime.Id nvarchar(100/0/0) 0
Unexpected item: VIEW dbo.ServerTaskLocalTime.JSON nvarchar(-1/0/0) 0
Unexpected item: VIEW dbo.ServerTaskLocalTime.Name nvarchar(100/0/0) 0
Unexpected item: VIEW dbo.ServerTaskLocalTime.ProjectId nvarchar(100/0/0) 1
Unexpected item: VIEW dbo.ServerTaskLocalTime.QueueTime datetime2(8/27/7) 1
Unexpected item: VIEW dbo.ServerTaskLocalTime.RunbookId nvarchar(100/0/0) 1
Unexpected item: VIEW dbo.ServerTaskLocalTime.ServerNodeId nvarchar(500/0/0) 1
Unexpected item: VIEW dbo.ServerTaskLocalTime.SpaceId nvarchar(100/0/0) 1
Unexpected item: VIEW dbo.ServerTaskLocalTime.StartTime datetime2(8/27/7) 1
Unexpected item: VIEW dbo.ServerTaskLocalTime.State nvarchar(100/0/0) 0
Unexpected item: VIEW dbo.ServerTaskLocalTime.TenantId nvarchar(100/0/0) 1
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_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_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_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_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_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
Missing item: IDX dbo.IX_Subscription_DataVersion DataVersion
Missing item: IDX dbo.IX_Subscription_DataVersion NONCLUSTERED 0 

[finnian.dempsey]

Hi @rjunruh,

Thanks for reaching out, sorry to hear you’re having trouble with Manual Intervention steps after upgrading to 2022.1!

Would you be able to please send through the complete log file for that Task? That should show us exactly what’s going on. The snippet you’ve sent through appears to show that this task was cancelled by user. Are you aware if this was the case, possibly because it was taking too long?

I’ll create and send through an SQL query for those missing indexes shortly, after running some quick tests on my local instance.

Feel free to reach out if you have any questions in the meantime!

Best Regards,

[finnian.dempsey]

Hi @rjunruh,

Just an update with the SQL required for the missing indexes, please make sure to take a full backup of the database and master key prior to running anything:

CREATE UNIQUE CLUSTERED INDEX [IX_Blob_BlobId] ON [dbo].[Blob]([ExtensionId] ASC,[BlobId] ASC)

CREATE NONCLUSTERED INDEX IX_DeploymentSettings_DataVersion ON dbo.[DeploymentSettings] ([DataVersion])

CREATE UNIQUE CLUSTERED INDEX IX_DeploymentSettings_SpaceId_ProjectId ON [dbo].[DeploymentSettings]([SpaceId] ASC,[ProjectId] ASC)

CREATE NONCLUSTERED INDEX [IX_EventRelatedDocument_EventId_RelatedDocumentIdPrefix] ON [dbo].[EventRelatedDocument]([EventId],[RelatedDocumentIdPrefix]) INCLUDE ([RelatedDocumentId])

CREATE NONCLUSTERED INDEX [IX_EventRelatedDocument_RelatedDocumentIdPrefix] ON [dbo].[EventRelatedDocument]([RelatedDocumentIdPrefix]) INCLUDE ([RelatedDocumentId], [EventId])

CREATE CLUSTERED INDEX [IX_HalibutMessageQueueItem_SequenceNumber] ON [dbo].[HalibutMessageQueueItem]([Endpoint] ASC,[Direction] ASC,[SequenceNumber] ASC)

CREATE NONCLUSTERED INDEX [IX_MachineScriptTask_BusinessProcess] ON [dbo].[MachineScriptTask]([LastModified] DESC,[BusinessProcessState] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

CREATE NONCLUSTERED INDEX [IX_MessageBusCursor_ConsumerGroupId] ON [dbo].[MessageBusCursor]([ConsumerGroupId] ASC)

CREATE CLUSTERED INDEX [IX_MessageBusEvent_SequenceNumber] ON [dbo].[MessageBusEvent]([SequenceNumber] ASC)

CREATE NONCLUSTERED INDEX IX_Release_SpaceId_ProjectId_ChannelId_Assembled ON Release([SpaceId],[ProjectId],[ChannelId],[Assembled] DESC)INCLUDE([Id],[Version],[ProjectVariableSetSnapshotId],[ProjectDeploymentProcessSnapshotId])

CREATE NONCLUSTERED INDEX [IX_RunbookRunRelatedMachine_RunbookRun_Machine] ON [dbo].[RunbookRunRelatedMachine]([MachineId] ASC) INCLUDE ([RunbookRunId])

CREATE NONCLUSTERED INDEX [IX_ServerTask_BusinessProcess] ON [dbo].[ServerTask]([SpaceId] ASC,[LastModified] ASC,[BusinessProcessState] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

CREATE NONCLUSTERED INDEX IX_Subscription_DataVersion ON dbo.[Subscription] ([DataVersion])

Feel free to reach out if there are any issues!

Best Regards,

[rjunruh]

This script worked to get the approval step to complete.

Thanks!