Compress operation fails after encountering a corrupt spatial index when a SQL Server 2008 R2 database is restored to SQL Server 2012

Description

For SQL Server 2008 R2 databases restored to a SQL Server 2012 instance, the Compress geodatabase operation fails with the error message:

"Failed to compress the database.

Underlying DBMS error [HY000:[Microsoft][SQL Server Native Client 11.0]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.] [sde.DEFAULT]"

Cause

A SQL Server Profiler trace (with Exceptions captured), run during the Compress, reveals an error about a corrupt index similar to the following:

"Unable to find index entry in index ID X, of table 1934681990, in database 'DatabaseName'. 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."

One or more indexes have been marked as corrupt, preventing the Compress from completing successfully.

Workaround

Contact Esri Technical Support or work with the DBA to rebuild spatial indexes on database tables.