Compress versioned database fails with underlying DBMS error?

Question

We are after migrating 2 geodatabase from SQL server 2008, one to SQL Server 2014, the other to SQL Server 2016. Everything appeared to be normal at first but we just noticed that the weekly compress is failing on both databases. The compress process, run from ArcCatalog, runs for a few minutes and then fails with an underlying DBMS error. When I check the compress log I can see a record for that compress with a compress_start time but no finish time. The compress status is 'TRIMMING' and the before and after state count has only gone down by a small number.

Has anybody come across this problem before?

Answer 1

Turns out it was a problem with the spatial indexes. The databases are using the SQL Server geometry spatial type. The database logs recorded an error and a crashed process when querying an index:

Unable to find index entry in index ID 1, of table 1146004851, in database 'SDW'. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support.

Once I figured out what table this was refrerring to I ran DBCC CHECKTABLE but this showed no problem with the index. However when I rebuilt the index and ran the compress again that table got compressed, but the compress failed on the next one. In the end I recreated all indexes using the geoprocessing tool and then the compress through to completion.

I think the root of the problem is that the spatial indexes were created in SQL Server 2008. At SQL server 2012 a change was made internally to how the spatial indexes behave. Hope this helps anyone else having the problem.

EDIT Here is the query I used to figure out what table the index is on based on the table id given in the sql log files

Select object_Name(Id) IndexName,
  object_name(parent_Obj) Tablename
From SysObjects
where object_name(id) like object_name(1146004851)
order By object_name(parent_Obj), 
         object_Name(Id)