Schema failure in System Integrity Check

[sbrewerton (Scott)]

Hi all

Apologies for a possible duplicate post, I’m not sure if we are an exact match for the other occurences of this

Getting a schema failure on our Octopus installation

Server version: 2018.7.5+Branch.master.Sha.ce16d8d3a19d4f30c9c736d7eec5de98c8a2609d
Server node:

                | Failed: Check System Integrity

15:53:57 Info | Unexpected related document id format… [ Passed ]
15:53:57 Error | Schema… [ Failed ]
15:53:58 Info | Release channel belongs to the appropriate project… [ Passed ]
15:53:58 Info | Orphaned tenanted deployments… [ Passed ]
15:53:58 Info | Orphaned channels… [ Passed ]
15:53:58 Info | Orphaned releases… [ Passed ]
15:53:58 Info | Only one unfrozen deployment process per project… [ Passed ]
15:53:58 Fatal | One or more data errors were found.
|
| Failed: Schema
15:53:57 Error | Unexpected item: IDX dbo.PK_CommandLog CLUSTERED 0
| Unexpected item: IDX dbo.PK_CommandLog ID
| Unexpected item: USER_TABLE dbo.CommandLog.Command nvarchar(-1/0/0) 0
| Unexpected item: USER_TABLE dbo.CommandLog.CommandType nvarchar(120/0/0) 0
| Unexpected item: USER_TABLE dbo.CommandLog.DatabaseName sysname(256/0/0) 1
| Unexpected item: USER_TABLE dbo.CommandLog.EndTime datetime(8/23/3) 1
| Unexpected item: USER_TABLE dbo.CommandLog.ErrorMessage nvarchar(-1/0/0) 1
| Unexpected item: USER_TABLE dbo.CommandLog.ErrorNumber int(4/10/0) 1
| Unexpected item: USER_TABLE dbo.CommandLog.ExtendedInfo xml(-1/0/0) 1
| Unexpected item: USER_TABLE dbo.CommandLog.ID int(4/10/0) 0
| Unexpected item: USER_TABLE dbo.CommandLog.IndexName sysname(256/0/0) 1
| Unexpected item: USER_TABLE dbo.CommandLog.IndexType tinyint(1/3/0) 1
| Unexpected item: USER_TABLE dbo.CommandLog.ObjectName sysname(256/0/0) 1
| Unexpected item: USER_TABLE dbo.CommandLog.ObjectType char(2/0/0) 1
| Unexpected item: USER_TABLE dbo.CommandLog.PartitionNumber int(4/10/0) 1

We are hosting Octopus on an Azure IaaS Vm running Server 2016 and using an Azure PaaS MS SQL DB for the backend (again, I have seen comments of Azure adding things so mentioning in case it’s a factor)

Grateful for any input/ideas

Many thanks

_scott

[sbrewerton (Scott)]

I have run the SQL query I found in the other cases

Had to attach as a file as output was quite a lot

SQL-query-output.txt (37.6 KB)

Thanks

_scott

[Alex.Rolley]

Hi @sbrewerton

Thanks for getting in touch.

What you are seeing there is an unknown index (IDX dbo.PK_COmmandLog), and an unknown database table (dbo.CommandLog), and by unknown we mean “not part of the default Octopus database structure”.

While I personally haven’t seen these tables before, it seems that someone has run a set of maintenance scripts known as SQL Server Maintenance Solution (see here) on your Octopus database (this is what gave me a clue).

While not recommended, if you haven’t seen any adverse side effects you should be OK to leave the tables and infrastructure alone, however they may be removed by our upgrade scripts as part of an upgrade in future.

Hopefully, that helps, please let me know if there is anything else I can assist with!

Regards,
Alex