Server log and system integrity failing regarding Runbook

[cwh]

Hi, we are getting some errors in our Octopus v2019.13.7 setup.

One is in the server logs:

Error when running scheduled task: Octopus.Server.Schedules.RunOnAScheduleAdapter1[Octopus.Server.Schedules.UpdateRunbookRunHistory] System.Exception: Error while executing SQL command in transaction 'UpdateRunbookRunHistory': Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The command being executed was: UpdateRunbookRunHistory ---> System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out --- End of inner exception stack trace --- at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteScalar() at Nevermore.Transient.IDbCommandExtensions.<>c__DisplayClass11_0.<ExecuteScalarWithRetry>b__0() at Nevermore.Transient.RetryPolicy.ExecuteAction[TResult](Func1 func)
at Nevermore.RelationalTransaction.ExecuteScalar[T](String query, CommandParameterValues args, Nullable1 commandTimeout) --- End of inner exception stack trace --- at Nevermore.RelationalTransaction.ExecuteScalar[T](String query, CommandParameterValues args, Nullable1 commandTimeout)
at Octopus.Server.Web.Infrastructure.OctopusQueryExecutor.ExecuteScalar[TResult](String query, CommandParameterValues args, Nullable`1 commandTimeout) in C:\buildAgent\work\abb2fbfce959a439\source\Octopus.Server\Web\Infrastructure\OctopusQueryExecutor.cs:line 73
at Octopus.Server.Schedules.UpdateRunbookRunHistory.Trigger() in C:\buildAgent\work\abb2fbfce959a439\source\Octopus.Server\Schedules\UpdateRunbookRunHistory.cs:line 47
at Octopus.Server.Schedules.Scheduler.RunTask(IRunOnASchedule schedule) in C:\buildAgent\work\abb2fbfce959a439\source\Octopus.Server\Schedules\Scheduler.cs:line 243

And the other is in system integrity where scheme failed:

Unexpected item: VIEW dbo.RunbookSnapshot_WithRunbookProcess.Project_Name nvarchar(400/0/0) 0
Unexpected item: VIEW dbo.RunbookSnapshot_WithRunbookProcess.Runbook_Name nvarchar(400/0/0) 0
Unexpected item: VIEW dbo.RunbookSnapshot_WithRunbookProcess.RunbookProcess_Id nvarchar(300/0/0) 0
Unexpected item: VIEW dbo.RunbookSnapshot_WithRunbookProcess.RunbookProcess_IsFrozen bit(1/1/0) 0
Unexpected item: VIEW dbo.RunbookSnapshot_WithRunbookProcess.RunbookProcess_JSON nvarchar(-1/0/0) 0
Unexpected item: VIEW dbo.RunbookSnapshot_WithRunbookProcess.RunbookProcess_OwnerId nvarchar(100/0/0) 0
Unexpected item: VIEW dbo.RunbookSnapshot_WithRunbookProcess.RunbookProcess_ProjectId nvarchar(100/0/0) 0
Unexpected item: VIEW dbo.RunbookSnapshot_WithRunbookProcess.RunbookProcess_SpaceId nvarchar(100/0/0) 0
Unexpected item: VIEW dbo.RunbookSnapshot_WithRunbookProcess.RunbookProcess_Version int(4/10/0) 0
Unexpected item: VIEW dbo.RunbookSnapshot_WithRunbookProcess.RunbookSnapshot_Assembled datetimeoffset(10/34/7) 0
Unexpected item: VIEW dbo.RunbookSnapshot_WithRunbookProcess.RunbookSnapshot_FrozenProjectVariableSetId nvarchar(300/0/0) 0
Unexpected item: VIEW dbo.RunbookSnapshot_WithRunbookProcess.RunbookSnapshot_FrozenRunbookProcessId nvarchar(300/0/0) 0
Unexpected item: VIEW dbo.RunbookSnapshot_WithRunbookProcess.RunbookSnapshot_Id nvarchar(300/0/0) 0
Unexpected item: VIEW dbo.RunbookSnapshot_WithRunbookProcess.RunbookSnapshot_JSON nvarchar(-1/0/0) 0
Unexpected item: VIEW dbo.RunbookSnapshot_WithRunbookProcess.RunbookSnapshot_Name nvarchar(400/0/0) 0
Unexpected item: VIEW dbo.RunbookSnapshot_WithRunbookProcess.RunbookSnapshot_Notes nvarchar(400/0/0) 1
Unexpected item: VIEW dbo.RunbookSnapshot_WithRunbookProcess.RunbookSnapshot_ProjectId nvarchar(300/0/0) 0
Unexpected item: VIEW dbo.RunbookSnapshot_WithRunbookProcess.RunbookSnapshot_RunbookId nvarchar(100/0/0) 0
Unexpected item: VIEW dbo.RunbookSnapshot_WithRunbookProcess.RunbookSnapshot_SpaceId nvarchar(100/0/0) 0

[paul.calvert]

Hi @cwh,

Thanks for getting in touch!

The system integrity check seems to be having a problem with a single View dbo.RunbookSnapshot_WithRunbookProcess. You should be able to manually remove this view from the Octopus database. It would be worth backing up the database beforehand, just in case.

Regarding the task failure, that is running a Stored Procedure within the database (dbo.UpdateRunbookRunHistory) that is being run and the timeout error suggests that the tables that it interacts with aren’t performing as expected.
It would be worth checking the general health of the database, such as index fragmentation, to ensure that the queries are being run at full efficiency.

You can also view the Stored Procedure that is being run within the database to help you isolate where the problem area may be.

Regards,
Paul