Error while executing SQL command in transaction

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.

Hi @Wildpipe

I’m sorry that you’ve hit this issue. We have seen this issue a few times and the engineers are hard at work, making sure that this won’t happen in future versions.

Before performing any work on the Database directly, we always recommend doing a database backup.
I think I’ve covered all of the missing indexes with this script:

CREATE INDEX IX_BuildInformationCreatedDate ON BuildInformation(CreatedDate)
CREATE INDEX IX_BuildInformationPackageIdPerSpace ON BuildInformation(SpaceId, PackageId)
CREATE INDEX IX_DeploymentSettings_DataVersion ON DeploymentSettings(DataVersion)
CREATE UNIQUE CLUSTERED INDEX IX_DeploymentSettings_SpaceId_ProjectId ON DeploymentSettings(ProjectId, SpaceId)
CREATE INDEX IX_EventRelatedDocument_EventId_RelatedDocumentIdPrefix ON EventRelatedDocument(EventId, RelatedDocumentIdPrefix, RelatedDocumentId)
CREATE INDEX IX_EventRelatedDocument_RelatedDocumentIdPrefix ON EventRelatedDocument(RelatedDocumentIdPrefix, RelatedDocumentId, EventId)
CREATE INDEX IX_ProjectTrigger_RunbookId ON ProjectTrigger(SpaceId, RunbookId)
CREATE INDEX IX_Release_SpaceId_ProjectId_ChannelId_Assembled ON Release(SpaceId, ProjectId, ChannelId, Assembled, Id, Version, ProjectVariableSetSnapshotId, ProjectDeploymentProcessSnapshotId)
CREATE INDEX IX_Runbook_DataVersion ON Runbook(SpaceId, DataVersion)
CREATE INDEX IX_Runbook_ProjectId ON Runbook(ProjectId)
CREATE INDEX IX_Runbook_Published_PublishedRunbookSnapshotId ON Runbook(SpaceId, PublishedRunbookSnapshotId)
CREATE INDEX IX_Runbook_SpaceId ON Runbook(SpaceId)
CREATE INDEX IX_RunbookProcess_ProjectId ON RunbookProcess(ProjectId)
CREATE INDEX IX_RunbookProcess_SpaceId ON RunbookProcess(SpaceId)
CREATE INDEX IX_RunbookRun_Index ON RunbookRun(RunbookSnapshotId, TaskId, EnvironmentId, Id, ProjectId, Name, Created)
CREATE INDEX IX_RunbookRun_ProjectId ON RunbookRun(ProjectId)
CREATE INDEX IX_RunbookRun_RunbookId ON RunbookRun(RunbookId)
CREATE INDEX IX_RunbookRun_SpaceId ON RunbookRun(SpaceId)
CREATE INDEX IX_RunbookRun_TenantId ON RunbookRun(TenantId)
CREATE INDEX IX_RunbookRun_UpdateRunbookRunHistory ON RunbookRun(TaskId, Name, EnvironmentId, ProjectId, RunbookId, RunbookSnapshotId, DeployedBy, TenantId, Created)
CREATE INDEX IX_RunbookRunHistory_IsPublished ON RunbookRunHistory(IsPublished)
CREATE INDEX IX_RunbookSnapshot_Assembled ON RunbookSnapshot(Assembled)
CREATE INDEX IX_RunbookSnapshot_DataVersion ON RunbookSnapshot(DataVersion)
CREATE INDEX IX_RunbookSnapshot_FrozenRunbookProcessId ON RunbookSnapshot(FrozenRunbookProcessId)
CREATE INDEX IX_RunbookSnapshot_ProjectId ON RunbookSnapshot(ProjectId)
CREATE INDEX IX_RunbookSnapshot_RunbookId ON RunbookSnapshot(RunbookId)
CREATE INDEX IX_RunbookSnapshot_SpaceId ON RunbookSnapshot(SpaceId)
CREATE INDEX IX_Subscription_DataVersion ON Subscription(DataVersion)
CREATE INDEX IX_Tenant_ClonedFromTenantId ON Tenant(ClonedFromTenantId)
CREATE INDEX IX_ScopedUserRole_SpaceDataVersion ON ScopedUserRole(SpaceId, DataVersion)
CREATE INDEX IX_Space_DataVersion ON Space(DataVersion)
CREATE INDEX IX_Team_SpaceDataVersion ON Team(SpaceId, DataVersion)
CREATE INDEX IX_UserRole_DataVersion ON UserRole(DataVersion)

Please run this and try your system integrity check again.

Regards,

Thank you! After creating the indexes the system integrity check no longer shows any errors and the Apply retention policies job finishes successfully.

1 Like