Hi,
Since Friday the 22nd our Octopus Deploy server has been throwing the following errors during execution of the Apply retention policies job:
Error while executing SQL command in transaction 'ApplyRetentionPoliciesTaskController|ServerTasks-234265-c57c6bbd-242a-411e-acda-587f22fafc20|T147': 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)
System.Exception
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 66
at Octopus.Core.Auditing.EventStore.Store(IRawWriteQueryExecutor session, Event event) in EventStore.cs:line 60
at Octopus.Core.BuiltInFeed.BuiltInPackageRepository.<>c__DisplayClass17_0.<RemovePackage in BuiltInPackageRepository.cs:line 233
at Octopus.Server.Infrastructure.Orchestration.UnitsOfWork.UnitOfWorkExecutor.<>c__DisplayClass3_0`1.<Execute in UnitOfWorkExecutor.cs:line 61
at Octopus.Core.Infrastructure.UnitsOfWork.UnitOfWorkExtensionMethods.DoAsync(IUnitOfWork unitOfWork, Func`1 action, CancellationToken cancellationToken, String name) in UnitOfWorkExtensionMethods.cs:line 73
at Octopus.Core.Infrastructure.UnitsOfWork.UnitOfWorkExtensionMethods.DoAsync(IUnitOfWork unitOfWork, Func`1 action, CancellationToken cancellationToken, String name) in UnitOfWorkExtensionMethods.cs:line 73
at Octopus.Server.Infrastructure.Orchestration.UnitsOfWork.UnitOfWorkExecutor.Execute[T](Func`3 action, CancellationToken cancellationToken, String name) in UnitOfWorkExecutor.cs:line 62
at Nito.AsyncEx.Synchronous.TaskExtensions.WaitAndUnwrapException(Task task)
at Octopus.Core.BuiltInFeed.BuiltInPackageRepository.RemovePackage(IndexedPackage package, Func`2 buildDeleteAuditEvent) in BuiltInPackageRepository.cs:line 224
at Octopus.Core.BuiltInFeed.BuiltInPackageRepository.<>c__DisplayClass16_0.<RemovePackagesWhere>b__1() in BuiltInPackageRepository.cs:line 213
at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--Inner Exception--
SQL Error -2 - Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.
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.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Nevermore.Transient.DbCommandExtensions.<>c__DisplayClass0_0.<ExecuteNonQueryWithRetry>b__0()
at Nevermore.Transient.RetryPolicy.ExecuteAction[TResult](Func`1 func)
at Nevermore.CommandExecutor.ExecuteNonQuery()
--Inner Exception--
The wait operation timed out.
System.ComponentModel.Win32Exception
I’ve run a System Integrity Check and Octopus Deploy discovered the following missing indexes:
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
I’m not sure how these indexes disappeared but just before the errors started appearing we applied the latest Windows security updates and restarted the server. We did not patch the Octopus Deploy software though so I’m not sure how a simple reboot of the server could make indexes disappear. Could you help us with some code to add these indexes back again?
We are running version 2021.2.7713.