SQL Execution Timeout Expired

Octopus Server
, ,
1

Hi,

Since Monday the 18th our Octopus Deploy server has been throwing the following errors during execution of the Apply retention policies job:

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 'ApplyRetentionPoliciesTaskController|ServerTasks-264688-277cbd2c-8f94-4a92-ac0b-b2e7c07f74e2|T95' Open with 3 commands started at 2022-07-26T08:26:16 (60.01 seconds ago)
2022-07-26T08:26:16 DELETE FROM [dbo].[NuGetPackage] WITH (ROWLOCK) WHERE [Id] = @Id

2022-07-26T08:26:16 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)

2022-07-26T08:26:16 DELETE FROM [dbo].[EventRelatedDocument] 
WHERE ([EventId] = @eventid)

System.Exception
   at Nevermore.CommandExecutor.WrapException(Exception ex)
   at Nevermore.CommandExecutor.ExecuteNonQuery()
   at Nevermore.Advanced.ReadTransaction.ExecuteNonQuery(PreparedCommand preparedCommand)
   at Nevermore.Querying.DeleteQueryBuilder`1.Delete(DeleteOptions options)
   at Octopus.Core.RelationalStorage.RelatedDocumentStore.PopulateEventRelatedDocuments(IWriteQueryExecutor transaction, Event event) in RelatedDocumentStore.cs:line 139
   at Octopus.Core.Auditing.EventStore.Store(IRawWriteQueryExecutor session, Event event) in EventStore.cs:line 60
   at Octopus.Core.BuiltInFeed.BuiltInPackageRepository.<>c__DisplayClass18_0.<RemovePackage in BuiltInPackageRepository.cs:line 268
   at Octopus.Server.Infrastructure.Orchestration.UnitsOfWork.UnitOfWorkExecutor.<>c__DisplayClass3_0`1.<Execute in UnitOfWorkExecutor.cs:line 87
   at Octopus.Core.Infrastructure.UnitsOfWork.UnitOfWorkExtensionMethods.Do(IUnitOfWork unitOfWork, Func`1 action, CancellationToken cancellationToken, String name) in UnitOfWorkExtensionMethods.cs:line 75
   at Octopus.Core.Infrastructure.UnitsOfWork.UnitOfWorkExtensionMethods.Do(IUnitOfWork unitOfWork, Func`1 action, CancellationToken cancellationToken, String name) in UnitOfWorkExtensionMethods.cs:line 75
   at Octopus.Server.Infrastructure.Orchestration.UnitsOfWork.UnitOfWorkExecutor.Execute[T](Func`3 action, CancellationToken cancellationToken, String name) in UnitOfWorkExecutor.cs:line 90
   at Nito.AsyncEx.Synchronous.TaskExtensions.WaitAndUnwrapException(Task task)
   at Octopus.Core.BuiltInFeed.BuiltInPackageRepository.RemovePackage(IndexedPackage package, Func`2 buildDeleteAuditEvent) in BuiltInPackageRepository.cs:line 273
   at Octopus.Core.BuiltInFeed.BuiltInPackageRepository.<>c__DisplayClass17_0.<RemovePackagesWhere>b__1() in BuiltInPackageRepository.cs:line 244
   at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)

I’ve run a System Integrity Check and Octopus Deploy discovered the following missing indexes:

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_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_DeploymentApproval_Deployment EnvironmentId
Missing item: IDX dbo.IX_DeploymentApproval_Deployment LastModified
Missing item: IDX dbo.IX_DeploymentApproval_Deployment NONCLUSTERED 0
Missing item: IDX dbo.IX_DeploymentApproval_Deployment ReleaseId
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_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_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_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_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_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_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_User_DataVersion DataVersion
Missing item: IDX dbo.IX_User_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_UserRole_DataVersion DataVersion
Missing item: IDX dbo.IX_UserRole_DataVersion NONCLUSTERED 0

We ran Windows Update on the server Friday the 15th but did not patch the Octopus Deploy software. Not sure why indexes would suddenly go missing after a few days since reboot of the server. Could you help us with some code to add these indexes back again?

We are running version 2022.2.6729

Thank you!

3

Hey @Wildpipe,

Thank you for contacting Octopus Support and sorry to hear you are having issues with your Octopus Instance. Thank you for running the Integrity check, it is one of the first things we would have asked you to do in order to try and rectify the retention policy errors you are getting in the logs.

I have attached the script you need to run on your DB to re-create the missing indexes. As always with running ANY db script please take a backup of the DB before running it just in case anything goes wrong and you need to revert back to the backup.

Please note, the script attached is just for your instance for the errors you are getting, if any other customer has an index issue please reach out and we will create you a bespoke script based on your errors and Octopus version.

Let me know what the outcome of running that script has on the Integrity check (if you can re-run it after running the script just to make sure all of the indexes have been re-created that would be brilliant).

Once that check passes hopefully the errors you are getting with retention policies will no longer exist but please do let us know if they still error out and we can look into that for you.

Kind Regards,

Clare
FixedIndexes.txt (14.1 KB)

4

Thanks for helping out. I tried running the script and encountered the following error:

Msg 156, Level 15, State 1, Line 45
Incorrect syntax near the keyword 'User'.
Msg 319, Level 15, State 1, Line 45
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
5

Hey @Wildpipe,

Sorry its taken awhile to get back to you, that error is new so it took awhile for us to figure out why it was erroring out. Usually those index scripts just work but it seems like this one gave us a hard time.

I have uploaded a new script for you which should now work fine, let me know if you have any issues with it and also backup the DB before running it just in case.

FixedIndexes - Updated.txt (14.1 KB)

Kind Regards,

Clare

6

Hey,

I tried running the new script and got the following error:

Msg 155, Level 15, State 1, Line 46
'OPTIMIZE_FOR_SEQUENTIAL_KEY' is not a recognized CREATE INDEX option.

The database server is running MSSQL Express 2016 version 14.0.3356.20. Is it safe to just remove the OPTIMIZE_FOR_SEQUENTIAL_KEY option?

7

Hey @Wildpipe,

We are not having much luck with that script are we! Sorry for that, I am using SQLServer2019 so that might be why that discrepancy exists. Not many customers use the express version due to DB size limitations so I do apologise for the errors in your script.

Please replace the last line with:

CREATE NONCLUSTERED INDEX [IX_User_DataVersion] ON [dbo].[User]( [DataVersion] 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 ) ON [PRIMARY]

This should then help you run it. If not I will go ahead and install the same SQL version you have to see if I can create the re-index query with that.

Let me know if this now works with changing that line and also if the re-index shows as passing.

Kind Regards,

Clare

8

That worked! Thank you so much. The retention jobs now finishes successfully in Octopus Deploy.

9

Hey @Wildpipe,

Fantastic news that did the trick for you! Good to know it fixed the retention policy task and that’s now running too.

Again sorry for the confusion surrounding the script errors but we got there in the end.

Please reach out if you have any further issues or queries and we will do our best to help.

Kind Regards,

Clare

10

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