SQL timeout ApplyRetentionPolicies

jonny.andersson · 18 November 2022 12:39

Hi, this is a follow up to:

We upgraded from 2021.3.12132 to 2022.3.10723 this morning and we are still experiencing problems when the apply retention policies job is running.

Regards,
Jonny

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Current transactions: Transaction ‘ApplyRetentionPoliciesTaskController|ServerTasks-1149407-0732b0b9-40e2-4936-89a6-7ba372fbd652|T91’ Open with 201 commands started at 2022-11-18T13:00:07 (794.83 seconds ago) 2022-11-18T13:00:07 SELECT * FROM Release_WithDeploymentProcess WHERE Release_SpaceId=@spaceId 2022-11-18T13:02:19 SELECT CAST(CHECKSUM_AGG(CAST(HASHBYTES(‘MD5’, CAST([DataVersion] AS varbinary)) AS int)) AS binary(8)) AS [Latest], COUNT(*) AS [Count], ‘Project’ AS [TableName], [SpaceId] AS [PartitionId] FROM [dbo].[Project] WHERE (((([SpaceId] in (‘Spaces-1’))))) AND (([SpaceId] IN (‘Spaces-1’))) GROUP BY [SpaceId]

and more but the text was too long to paste here…

clare.martin · 18 November 2022 12:50

Good afternoon @jonny.andersson,

Thank you for contacting Octopus Support and sorry to hear you are seeing issues with the retention policy task timing out.

I noticed you put a ticket in for this over on our zendesk platform so, to save duplication we will continue to troubleshoot through email if that is ok.

Once we get that fixed for you I will post up the troubleshooting process here in case another customer runs into this issue.

Kind Regards,
Clare

jonny.andersson · 18 November 2022 12:51

Sounds good, thanks.

cloud-devops · 7 December 2022 21:29

Hi,

We are also experiencing this issue, would like to know when a solution is found.

Thanks

Paul

Daniel_Fischer · 8 December 2022 00:48

Hi Paul,

I have a link for the GitHub issue that you can follow for updates. When resolved and released, we will update the issue with the Octopus version containing the fix.

github.com/OctopusDeploy/Issues

Deployment timing out when retention is running

opened 01:33AM - 21 Nov 22 UTC

veochen-octopus

kind/bug p2 state/backlog team/fire-and-motion

### Team - [X] I've assigned a team label to this issue ### Severity an…noying ### Version 2022.3. probably any. ### Latest Version I could reproduce the problem in the latest build ### What happened? Deployment occasionally times out when retention is running at the same time. Specifically it happens when the deployment is inserting into `DeploymentRelatedMachine`. This seems related to https://github.com/OctopusDeploy/Issues/issues/7435 ### Reproduction See thread and ticket for details. ### Error and Stacktrace ```shell | 2022-11-18T12:27:22 INSERT INTO [dbo].[DeploymentRelatedMachine] ([DeploymentId], [MachineId]) VALUES | (@0__DeploymentId, @0__MachineId) | ,(@1__DeploymentId, @1__MachineId) | ,(@2__DeploymentId, @2__MachineId) | ,(@3__DeploymentId, @3__MachineId) | ,(@4__DeploymentId, @4__MachineId) | Transaction 'SpacesCache' Open with 0 commands started at 2022-11-18T12:28:22 (0.02 seconds ago) | System.Exception | at Nevermore.CommandExecutor.WrapException(Exception ex) | at Nevermore.CommandExecutor.ExecuteNonQueryAsync(CancellationToken cancellationToken) | at Nevermore.Advanced.ReadTransaction.ExecuteNonQueryAsync(PreparedCommand preparedCommand, CancellationToken cancellationToken) | at Nevermore.Advanced.WriteTransaction.ExecuteDataModificationAsync(PreparedCommand command, CancellationToken cancellationToken) | at Nevermore.Advanced.WriteTransaction.InsertManyAsync[TDocument](IReadOnlyCollection`1 documents, InsertOptions options, CancellationToken cancellationToken) | at Octopus.Core.RelationalStorage.RelatedDocumentStore.PopulateDeploymentRelatedMachines(IWriteQueryExecutor transaction, Deployment deployment, CancellationToken cancellationToken) in RelatedDocumentStore.cs:line 101 | at Octopus.Core.RelationalStorage.RelatedDocumentStore.PopulateRelatedDocumentsAsync[TDocument](IWriteTransaction transaction, TDocument instance, CancellationToken cancellationToken) in RelatedDocumentStore.cs:line 49 | at Nevermore.Advanced.WriteTransaction.UpdateAsync[TDocument](TDocument document, UpdateOptions options, CancellationToken cancellationToken) | at Octopus.Core.RelationalStorage.RawRelationalTransaction.Update[TKey](IId`1 document, CancellationToken cancellationToken, UpdateOptions options) in RawRelationalTransaction.cs:line 162 | at Octopus.Server.Web.Infrastructure.OctopusQueryExecutor.Update[TKey](IId`1 instance, CancellationToken cancellationToken, UpdateOptions options) in OctopusQueryExecutor.cs:line 333 | at Octopus.Core.Persistence.Database.ProjectPathDecorators.ProjectPathQueryExecutorDecorator.Update[TKey](IId`1 document, CancellationToken cancellationToken, UpdateOptions options) in ProjectPathQueryExecutorDecorator.cs:line 159 | at Octopus.Core.Persistence.Database.SlugDecorators.SlugQueryExecutorDecorator.Update[TKey](IId`1 document, CancellationToken cancellationToken, UpdateOptions options) in SlugQueryExecutorDecorator.cs:line 155 | at Octopus.Server.Orchestration.ServerTasks.Deploy.DeploymentPlanService.SaveStateContainer(Deployment execution, CancellationToken cancellationToken) in DeploymentPlanService.cs:line 112 | at Octopus.Server.Orchestration.ServerTasks.Deploy.ExecutionPlanService`3.Cleanup(TExecutionPlan plan, CancellationToken cancellationToken) in ExecutionPlanService.cs:line 126 | at Octopus.Server.Orchestration.ServerTasks.Deploy.DeploymentTaskController.<>c__DisplayClass6_0.<CleanupPlan in DeploymentTaskController.cs:line 68 | 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 93 | at Octopus.Core.Infrastructure.UnitsOfWork.UnitOfWorkExtensionMethods.Do(IUnitOfWork unitOfWork, Func`1 action, CancellationToken cancellationToken, String name) in UnitOfWorkExtensionMethods.cs:line 93 | at Octopus.Server.Infrastructure.Orchestration.UnitsOfWork.UnitOfWorkExecutor.Execute[T](Func`3 action, CancellationToken cancellationToken, String name) in UnitOfWorkExecutor.cs:line 90 | at Octopus.Server.Orchestration.ServerTasks.Deploy.DeploymentTaskController.CleanupPlan(DeploymentPlan plan, CancellationToken cancellationToken) in DeploymentTaskController.cs:line 70 | at Octopus.Server.Orchestration.ServerTasks.Deploy.ExecutionTaskController`3.ExecuteBase(CancellationToken cancellationToken) in ExecutionTaskController.cs:line 121 | at Octopus.Server.Orchestration.ServerTasks.Deploy.DeploymentTaskController.Execute(CancellationToken cancellationToken) in DeploymentTaskController.cs:line 60 | at Octopus.Server.Orchestration.ServerTasks.RunningTask.<>c__DisplayClass31_0.<WorkerTask in RunningTask.cs:line 175 | at Octopus.Core.Infrastructure.UnitsOfWork.UnitOfWorkExtensionMethods.Do(IUnitOfWork unitOfWork, Func`1 action, CancellationToken cancellationToken, String name) in UnitOfWorkExtensionMethods.cs:line 93 | at Octopus.Core.Infrastructure.UnitsOfWork.UnitOfWorkExtensionMethods.Do(IUnitOfWork unitOfWork, Func`1 action, CancellationToken cancellationToken, String name) in UnitOfWorkExtensionMethods.cs:line 93 | at Octopus.Server.Orchestration.ServerTasks.RunningTask.WorkerTask(CancellationToken cancellationToken) in RunningTask.cs:line 208 | --Inner Exception-- | SQL Error -2 - Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. | Microsoft.Data.SqlClient.SqlException | at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) | at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) | at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod) | at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult) | at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult) | at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization) | at Nevermore.Transient.DbCommandExtensions.<>c__DisplayClass1_0.<ExecuteNonQueryWithRetryAsync | at Nevermore.Transient.DbCommandExtensions.<>c__DisplayClass1_0.<ExecuteNonQueryWithRetryAsync | at Nevermore.CommandExecutor.ExecuteNonQueryAsync(CancellationToken cancellationToken) | --Inner Exception-- | The wait operation timed out. | System.ComponentModel.Win32Exception ``` ### More Information [slack](https://octopusdeploy.slack.com/archives/CNHBHV2BX/p1668786157126259) [zendesk](https://octopus.zendesk.com/agent/tickets/101843) [shortcut](https://app.shortcut.com/octopusdeploy/story/31831/deployment-timing-out-when-retention-is-running) ### Workaround As it only occasionally happens, a rerun should fix it most of the times

Best regards,
Daniel

system · 8 January 2023 00:49

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