SQL Timeout When Attempting to Execute Runbook

chriswine · 11 May 2022 17:16

We’re running version 2022.1 (Build 2495), and I’m trying to execute a Runbook I created. When I try to Run the Runbook, I get the following error after a time. We’re not sure what to do about this, any advice?

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 'CreateRunbookRun.Create|0HMHCHKVPL6E3:00000008|T160' Open with 14 commands started at 2022-05-11T16:42:30 (60.80 seconds ago) 2022-05-11T16:42:30 SELECT TOP 1 Id,Name,Notes,Assembled,ProjectId,RunbookId,FrozenProjectVariableSetId,FrozenRunbookProcessId,DataVersion,SpaceId,JSON FROM [dbo].[RunbookSnapshot] WHERE [Id] = @Id 2022-05-11T16:42:30 SELECT MAX([DataVersion]) AS [Latest], count(*) AS [Count], 'Runbook' AS [TableName], [SpaceId] AS [PartitionId] FROM [dbo].[Runbook] WHERE (((([SpaceId] in ('Spaces-1'))))) AND (([SpaceId] in ('Spaces-1'))) GROUP BY [SpaceId] 2022-05-11T16:42:30 SELECT TOP 1 Id,Username,IsActive,IsService,IdentificationToken,EmailAddress,ExternalId,ExternalIdentifiers,DisplayName,JSON FROM [dbo].[User] WHERE [Id] = @Id 2022-05-11T16:42:30 SELECT TOP 1 Id,Name,Notes,Assembled,ProjectId,RunbookId,FrozenProjectVariableSetId,FrozenRunbookProcessId,DataVersion,SpaceId,JSON FROM [dbo].[RunbookSnapshot] WHERE [Id] = @Id 2022-05-11T16:42:30 SELECT TOP 1 Id,Name,Slug,IsDisabled,VariableSetId,DeploymentProcessId,ProjectGroupId,LifecycleId,AutoCreateRelease,IncludedLibraryVariableSetIds,DiscreteChannelRelease,DataVersion,ClonedFromProjectId,SpaceId,LastModified,JSON FROM [dbo].[Project] WHERE [Id] = @Id 2022-05-11T16:42:30 SELECT TOP 1 Id,OwnerType,OwnerId,Version,IsFrozen,RelatedDocumentIds,SpaceId,JSON FROM [dbo].[VariableSet] WHERE [Id] = @Id 2022-05-11T16:42:30 SELECT TOP 1 Id,Name,Slug,IsDisabled,VariableSetId,DeploymentProcessId,ProjectGroupId,LifecycleId,AutoCreateRelease,IncludedLibraryVariableSetIds,DiscreteChannelRelease,DataVersion,ClonedFromProjectId,SpaceId,LastModified,JSON FROM [dbo].[Project] WHERE [Id] = @Id 2022-05-11T16:42:30 SELECT TOP 1 Id,Name,Slug,IsDisabled,VariableSetId,DeploymentProcessId,ProjectGroupId,LifecycleId,AutoCreateRelease,IncludedLibraryVariableSetIds,DiscreteChannelRelease,DataVersion,ClonedFromProjectId,SpaceId,LastModified,JSON FROM [dbo].[Project] WHERE [Id] = @Id 2022-05-11T16:42:30 INSERT INTO [dbo].[ServerTask] ([Id], [Name], [Description], [QueueTime], [StartTime], [CompletedTime], [ErrorMessage], [ConcurrencyTag], [State], [HasPendingInterruptions], [HasWarningsOrErrors], [ServerNodeId], [ProjectId], [EnvironmentId], [TenantId], [RunbookId], [DurationSeconds], [SpaceId], [LastModified], [BusinessProcessState], [ServerTaskType], [JSON]) VALUES (@Id, @Name, @Description, @QueueTime, @StartTime, @CompletedTime, @ErrorMessage, @ConcurrencyTag, @State, @HasPendingInterruptions, @HasWarningsOrErrors, @ServerNodeId, @ProjectId, @EnvironmentId, @TenantId, @RunbookId, @DurationSeconds, @SpaceId, @LastModified, @BusinessProcessState, @ServerTaskType, @JSON) 2022-05-11T16:42:30 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-05-11T16:42:30 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-05-11T16:42:30 DELETE FROM [dbo].[EventRelatedDocument] WHERE ([EventId] = @eventid) 2022-05-11T16:42:30 INSERT INTO [dbo].[EventRelatedDocument] ([EventId], [RelatedDocumentId], [RelatedDocumentIdPrefix]) VALUES (@0__EventId, @0__RelatedDocumentId, @0__RelatedDocumentIdPrefix) ,(@1__EventId, @1__RelatedDocumentId, @1__RelatedDocumentIdPrefix) ,(@2__EventId, @2__RelatedDocumentId, @2__RelatedDocumentIdPrefix) ,(@3__EventId, @3__RelatedDocumentId, @3__RelatedDocumentIdPrefix) 2022-05-11T16:42:30 SELECT TOP 1 Id,Name,Description,QueueTime,StartTime,CompletedTime,ErrorMessage,ConcurrencyTag,State,HasPendingInterruptions,HasWarningsOrErrors,ServerNodeId,ProjectId,EnvironmentId,TenantId,RunbookId,DurationSeconds,SpaceId,LastModified,BusinessProcessState,ServerTaskType,JSON FROM [dbo].[ServerTask] WHERE (((([SpaceId] in ('Spaces-1')) AND [Name] NOT IN ('ImportProjects', 'ExportProjects')))) AND ([ProjectId] = @p1) AND ([EnvironmentId] = @p2) AND ([State] = @p3) AND ([TenantId] is null) AND ([RunbookId] = @p4) ORDER BY [CompletedTime] DESC Transaction 'CreateRunbookRun.Create|0HMHCHKVPL6E3:00000008|T371' Open with 31 commands started at 2022-05-11T16:42:30 (60.77 seconds ago) 2022-05-11T16:42:30 SELECT TOP 1 Id,Name,ProjectId,RunbookProcessId,SpaceId,DataVersion,PublishedRunbookSnapshotId,JSON FROM [dbo].[Runbook] WHERE [Id] = @Id 2022-05-11T16:42:30 SELECT TOP 1 Id,Name,Notes,Assembled,ProjectId,RunbookId,FrozenProjectVariableSetId,FrozenRunbookProcessId,DataVersion,SpaceId,JSON FROM [dbo].[RunbookSnapshot] WHERE [Id] = @Id 2022-05-11T16:42:30 SELECT TOP 1 Id,OwnerId,ProjectId,IsFrozen,Version,SpaceId,JSON FROM [dbo].[RunbookProcess] WHERE [Id] = @Id 2022-05-11T16:42:30 SELECT TOP 1 Id,Name,Slug,IsDisabled,VariableSetId,DeploymentProcessId,ProjectGroupId,LifecycleId,AutoCreateRelease,IncludedLibraryVariableSetIds,DiscreteChannelRelease,DataVersion,ClonedFromProjectId,SpaceId,LastModified,JSON FROM [dbo].[Project] WHERE [Id] = @Id 2022-05-11T16:42:30 SELECT TOP 1 Id,Name,SortOrder,DataVersion,SpaceId,JSON FROM [dbo].[DeploymentEnvironment] WHERE [Id] = @Id 2022-05-11T16:42:30 SELECT TOP 1 Id,Name,DataVersion,SpaceId,JSON FROM [dbo].[ProjectGroup] WHERE [Id] = @Id 2022-05-11T16:42:30 SELECT TOP 1 Id,Name,IsDefault,TaskQueueStopped,DataVersion,LastModified,IsPrivate,JSON FROM [dbo].[Space] WHERE [Id] = @Id 2022-05-11T16:42:30 SELECT [Name] FROM [dbo].[RunbookRun] WHERE (((([SpaceId] in ('Spaces-1'))))) AND ([RunbookSnapshotId] = @runbooksnapshotid) AND ([EnvironmentId] = @environmentid) ORDER BY [Id] 2022-05-11T16:42:30 INSERT INTO [dbo].[RunbookRun] ([Id], [Name], [Created], [EnvironmentId], [ProjectId], [RunbookId], [RunbookSnapshotId], [TaskId], [TenantId], [DeployedBy], [IsPublished], [SpaceId], [JSON]) VALUES (@Id, @Name, @Created, @EnvironmentId, @ProjectId, @RunbookId, @RunbookSnapshotId, @TaskId, @TenantId, @DeployedBy, @IsPublished, @SpaceId, @JSON) 2022-05-11T16:42:30 DELETE FROM [dbo].[RunbookRunRelatedMachine] WHERE ([RunbookRunId] = @runbookrunid) 2022-05-11T16:42:30 SELECT TOP 1 Id,Name,Notes,Assembled,ProjectId,RunbookId,FrozenProjectVariableSetId,FrozenRunbookProcessId,DataVersion,SpaceId,JSON FROM [dbo].[RunbookSnapshot] WHERE (((([SpaceId] in ('Spaces-1'))))) AND ([Id] <> @releaseid AND [ProjectId] = @projectid) ORDER BY [Assembled] DESC 2022-05-11T16:42:30 SELECT TOP 1 Id,Name,Notes,Assembled,ProjectId,RunbookId,FrozenProjectVariableSetId,FrozenRunbookProcessId,DataVersion,SpaceId,JSON FROM [dbo].[RunbookSnapshot] WHERE (((([SpaceId] in ('Spaces-1'))))) AND ([Id] = (SELECT TOP 1 [RunbookSnapshotId] FROM [RunbookRun] WHERE [ProjectId] = @projectid AND [EnvironmentId] = @environmentid AND [RunbookSnapshotId] <> @releaseid ORDER BY [Created] DESC)) ORDER BY [Id] 2022-05-11T16:42:30 SELECT Id,Name,SortOrder,DataVersion,SpaceId,JSON FROM [dbo].[DeploymentEnvironment] WHERE (((([SpaceId] in ('Spaces-1'))))) ORDER BY [Id] 2022-05-11T16:42:30 SELECT TOP 1 Id,OwnerType,OwnerId,Version,IsFrozen,RelatedDocumentIds,SpaceId,JSON FROM [dbo].[VariableSet] WHERE [Id] = @Id 2022-05-11T16:42:30 SELECT TOP 1 Id,Name,Slug,IsDisabled,VariableSetId,DeploymentProcessId,ProjectGroupId,LifecycleId,AutoCreateRelease,IncludedLibraryVariableSetIds,DiscreteChannelRelease,DataVersion,ClonedFromProjectId,SpaceId,LastModified,JSON FROM [dbo].[Project] WHERE [Id] = @Id 2022-05-11T16:42:30 SELECT MAX([DataVersion]) AS [Latest], count(*) AS [Count], 'Machine' AS [TableName], [SpaceId] AS [PartitionId] FROM [dbo].[Machine] WHERE (((([SpaceId] in ('Spaces-1'))))) AND (([SpaceId] in ('Spaces-1'))) GROUP BY [SpaceId] 2022-05-11T16:42:31 SELECT Id,Name,Thumbprint,NotAfter,Subject,EnvironmentIds,TenantIds,TenantTags,Archived,Created,DataVersion,SpaceId,JSON FROM [dbo].[Certificate] WHERE (((([SpaceId] in ('Spaces-1'))))) AND (0 = 1) ORDER BY [Id] 2022-05-11T16:42:31 SELECT Id,AccountType,Name,EnvironmentIds,TenantIds,TenantTags,DataVersion,SpaceId,JSON FROM [dbo].[Account] WHERE (((([SpaceId] in ('Spaces-1'))))) AND (0 = 1) ORDER BY [Id] 2022-05-11T16:42:31 SELECT TOP 1 Id,Name,ProjectId,RunbookProcessId,SpaceId,DataVersion,PublishedRunbookSnapshotId,JSON FROM [dbo].[Runbook] WHERE [Id] = @Id 2022-05-11T16:42:31 SELECT TOP 1 Id,Name,Version,ActionType,CommunityActionTemplateId,SpaceId,JSON FROM [dbo].[ActionTemplate] WHERE [Id] = @Id 2022-05-11T16:42:31 SELECT TOP 1 Id,OwnerType,OwnerId,Version,IsFrozen,RelatedDocumentIds,SpaceId,JSON FROM [dbo].[VariableSet] WHERE [Id] = @Id 2022-05-11T16:42:31 SELECT TOP 1 Id,Name,Created,EnvironmentId,ProjectId,RunbookId,RunbookSnapshotId,TaskId,DeployedBy,TenantId,SpaceId,IsPublished,JSON FROM [dbo].[RunbookRun] WHERE [Id] = @Id 2022-05-11T16:42:31 INSERT INTO [dbo].[VariableSet] ([Id], [OwnerType], [OwnerId], [Version], [IsFrozen], [RelatedDocumentIds], [SpaceId], [JSON]) VALUES (@Id, @OwnerType, @OwnerId, @Version, @IsFrozen, @RelatedDocumentIds, @SpaceId, @JSON) 2022-05-11T16:42:31 SELECT TOP 1 Id,Name,Created,EnvironmentId,ProjectId,RunbookId,RunbookSnapshotId,TaskId,DeployedBy,TenantId,SpaceId,IsPublished,JSON FROM [dbo].[RunbookRun] WHERE [Id] = @Id 2022-05-11T16:42:31 UPDATE [dbo].[RunbookRun] SET [Name] = @Name, [Created] = @Created, [EnvironmentId] = @EnvironmentId, [ProjectId] = @ProjectId, [RunbookId] = @RunbookId, [RunbookSnapshotId] = @RunbookSnapshotId, [TaskId] = @TaskId, [TenantId] = @TenantId, [DeployedBy] = @DeployedBy, [IsPublished] = @IsPublished, [SpaceId] = @SpaceId, [JSON] = @JSON WHERE [Id] = @Id DELETE FROM [dbo].[RelatedDocument] WHERE [Id] = @Id 2022-05-11T16:42:31 DELETE FROM [dbo].[RunbookRunRelatedMachine] WHERE ([RunbookRunId] = @runbookrunid) 2022-05-11T16:42:31 SELECT Id,SpaceId,Name,FeedType,FeedUri,DataVersion,JSON FROM [dbo].[Feed] WHERE (((([SpaceId] in ('Spaces-1'))))) ORDER BY [Id] 2022-05-11T16:42:31 SELECT TOP 1 Id,Name,Version,ActionType,CommunityActionTemplateId,SpaceId,JSON FROM [dbo].[ActionTemplate] WHERE (((([SpaceId] in ('Spaces-1'))))) AND ([Id] = @id) AND ([Version] = @version) ORDER BY [Id] 2022-05-11T16:42:31 SELECT TOP 1 Id,Name,ProjectId,RunbookProcessId,SpaceId,DataVersion,PublishedRunbookSnapshotId,JSON FROM [dbo].[Runbook] WHERE [Id] = @Id 2022-05-11T16:42:31 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-05-11T16:42:31 DELETE FROM [dbo].[EventRelatedDocument] WHERE ([EventId] = @eventid)

britton.riggs · 11 May 2022 21:28

Hi,

Thanks for posting your question and welcome to the community! Sorry to hear that you are running into timeout issues in executing your runbook, but I will do my best to assist!

As a first step in troubleshooting, would you be able to run a system integrity check [1] and link the results back to me?

[1] Diagnostics - Octopus Deploy

Kind regards,

Britton

chriswine · 11 May 2022 21:57

Sure thing, here’s the results. It certainly looks like we’re missing some indexes somehow.

Task ID:        ServerTasks-151542
Related IDs:    
Task status:    Failed
Task queued:    Wednesday, 11 May 2022 9:53:55 PM +00:00
Task started:   Wednesday, 11 May 2022 9:53:56 PM +00:00
Task completed: Wednesday, 11 May 2022 9:53:59 PM +00:00
Task duration:  3 seconds
Server version: 2022.1.2495
Server node:    ip-10-xxx-xxx-xxx

                    | Failed: Check System Integrity
21:53:59   Info     |   Unexpected related document id format....................... [ Passed ]
21:53:59   Error    |   Schema...................................................... [ Failed ]
21:53:59   Info     |   Release channel belongs to the appropriate project.......... [ Passed ]
21:53:59   Info     |   Orphaned tenanted deployments............................... [ Passed ]
21:53:59   Info     |   Orphaned channels........................................... [ Passed ]
21:53:59   Info     |   Orphaned releases........................................... [ Passed ]
21:53:59   Info     |   Only one unfrozen deployment process per project............ [ Passed ]
21:53:59   Fatal    |   One or more data errors were found.
                    | 
                    |   Failed: Schema
21:53:59   Error    |     Unexpected item: VIEW dbo.View_QA_DeploymentHistory.CompletedTime datetimeoffset(10/34/7) 1
                    |     Unexpected item: VIEW dbo.View_QA_DeploymentHistory.Created datetimeoffset(10/34/7) 0
                    |     Unexpected item: VIEW dbo.View_QA_DeploymentHistory.DeployedBy nvarchar(400/0/0) 1
                    |     Unexpected item: VIEW dbo.View_QA_DeploymentHistory.DeploymentName nvarchar(400/0/0) 0
                    |     Unexpected item: VIEW dbo.View_QA_DeploymentHistory.EnvironmentName nvarchar(400/0/0) 0
                    |     Unexpected item: VIEW dbo.View_QA_DeploymentHistory.ProjectName nvarchar(400/0/0) 0
                    |     Unexpected item: VIEW dbo.View_QA_DeploymentHistory.ReleaseVersion nvarchar(200/0/0) 0
                    |     Unexpected item: VIEW dbo.View_QA_DeploymentHistory.StartTime datetimeoffset(10/34/7) 1
                    |     Unexpected item: VIEW dbo.View_QA_DeploymentHistory.TaskState nvarchar(100/0/0) 0
                    |     Unexpected item: VIEW dbo.View_QA_DeploymentHistory.TenantName nvarchar(400/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_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_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
                    |

chriswine · 11 May 2022 22:45

We’ve been able to compare to a working database, create the proper indexes, and are now back up and running. Thanks for the link, that made all the difference.

britton.riggs · 11 May 2022 22:51

Hi,

Thank you for providing those details! It looks like the Integrity Check is showing a few issues with missing indexes, which could definitely be causing the performance issues you are experiencing.

In order to resolve these issues you should first take a backup of your existing Octopus Deploy database prior to performing any remediation steps. To rebuild the missing indexes you will then need to run the following queries against your database:

CREATE UNIQUE CLUSTERED INDEX [IX_Blob_BlobId] ON [dbo].[Blob](ExtensionId ASC, BlobId ASC) 
WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF) ON [PRIMARY];

CREATE NONCLUSTERED INDEX [IX_DynamicInfrastructureLifecycle_BusinessProcess] ON [dbo].[DynamicInfrastructureLifecycle](LastModified DESC, BusinessProcessState ASC) 
WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF) ON [PRIMARY];

CREATE NONCLUSTERED INDEX [IX_EventRelatedDocument_EventId_RelatedDocumentIdPrefix] ON [dbo].[EventRelatedDocument](EventId ASC, RelatedDocumentIdPrefix ASC) 
INCLUDE (RelatedDocumentId)
WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF) ON [PRIMARY];

CREATE NONCLUSTERED INDEX [IX_EventRelatedDocument_RelatedDocumentIdPrefix] ON [dbo].[EventRelatedDocument](RelatedDocumentIdPrefix ASC) 
INCLUDE (RelatedDocumentId, EventId)
WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF) ON [PRIMARY];

CREATE NONCLUSTERED INDEX [IX_GitCredential_SpaceId] ON [dbo].[GitCredential](SpaceId ASC) 
WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF) ON [PRIMARY];

CREATE CLUSTERED INDEX [IX_HalibutMessageQueueItem_SequenceNumber] ON [dbo].[HalibutMessageQueueItem](SequenceNumber ASC, Endpoint ASC, Direction ASC) 
WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF) ON [PRIMARY];

CREATE NONCLUSTERED INDEX [IX_MachineHealthCheck_BusinessProcess] ON [dbo].[MachineHealthCheck](LastModified DESC, BusinessProcessState ASC) 
WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF) ON [PRIMARY];

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

CREATE NONCLUSTERED INDEX [IX_MessageBusCursor_ConsumerGroupId] ON [dbo].[MessageBusCursor](ConsumerGroupId ASC) 
WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF) ON [PRIMARY];

CREATE CLUSTERED INDEX [IX_MessageBusEvent_SequenceNumber] ON [dbo].[MessageBusEvent](SequenceNumber ASC) 
WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF) ON [PRIMARY];

CREATE NONCLUSTERED INDEX [IX_RunbookRunRelatedMachine_RunbookRun_Machine] ON [dbo].[RunbookRunRelatedMachine](MachineId ASC) 
INCLUDE (RunbookRunId)
WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF) ON [PRIMARY];

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

After running these queries I would then run another Integrity Check to confirm things look good (the check should no longer show missing items, but you will still see entries for unexpected items - this is expected ) and if so I would then try to execute your runbook once more.

Let me know how this goes, and if you run into any more trouble I will dive deeper on this issue.

Best regards,

Britton

britton.riggs · 11 May 2022 23:01

Hi,

I didn’t see your response initially, but I’m happy to hear that things are working as expected now!

Hope you have a good rest of your day,

Britton

system · 12 June 2022 09:41

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