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?
Best Answer
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:
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