Compress did not empty the A tables. Data in base.

[Scott_Tansley]

Hi.  My client has a SQL Server 2019 database and is using ArcMap 10.8.1.  The enterprise geodatabase is also at version 10.8.1.

The client has a simple point feature class with attachments enabled.  They are capturing photos into the attachment.

The attachment table has 5012 rows and the associated A table also has 5012 rows.  When we try to compress we receive an error which states that:

• Cannot insert duplicate key row in object 'featureclass' with unique index 'R87_SDE_ROWID_UK'.
• The duplicate key value is (25).::Unable to trim state 109 to 0 for table

I've reviewed various toolbox tools, including:

• Diagnose Version Metadata
• Diagnose Version
• Repair Version Metadata
• Repair Version

Each of which returns:

• Error executing function.
• A check constraint violation has occurred.

As mentioned above when I review the Base and A tables in SSMS, the counts are the same.  There are no records in the d table, and the client has said that they have only ever 'added' to the data.

When I run the following SQL:

   SELECT *

   FROM   [DB].[a191]

   WHERE  EXISTS (SELECT 1

                   FROM   [DB].[Table__ATTACH]

                   WHERE  [DB].[a191].[GLOBALID] = [DB].[Table__ATTACH].[GLOBALID])

I got 5012 rows, which shows every globalid in the A table is already in the base table and the unique id constraint is preventing it from being appended. 

This all makes complete sense.  What doesn't make sense is why the Compress tool has left it hanging in the A table.  The last successful compress was in November last year.  Each Compress since is logged as:

   FAILURE -51

Does anyone have a view on how to move forwards?  I'm currently thinking of using SSMS to truncate the A table, but feel that isn't the right way, but no Toolbox tools seem to be helping.

Hive mind, what do you think?

Many thanks in advance.

[MarceloMarques · Accepted solution]

Hello @Scott_Tansley 

Please open a ticket with Esri Technical Support and be prepared to provide a database backup to Esri Support to further troubleshoot the issue to try to find a workaround.

My 2 cents, worst case scenario you will need to unregister as versioned the featureclass, this will remove the A table and D tables and all the edits in the A, D will be lost, then delete all versions child of sde.default, then try to execute the sde compress again, last register as versioned the featureclass again, this will recreate the A table and D table.

Note, if someone created an attribute index in the featureclass via SSMS then this can be the cause why the sde compress is not working, any indexes must be created via ArcCatalog, especially when the featureclass is registered as versioned because only ArcCatalog understands that the registered as versioned featureclass has the base table, the A table and the D table. It can be difficult to find that one index that was created with SSMS. But Esri Tech Support will help troubleshoot further.

I hope this helps.

[Scott_Tansley]

Thank you.  I'll refer back to the client with this information.  They will have to agree to the database backup.  

Really appreciate the quick response.

[EdwardBlair]

Hello Scott -

Any chance that ESRI tech support got back to you with a resolution to this issue?   I'm working with an organization that is getting the same errors - albeit in Oracle.   There are no non-ESRI indexes on the table where the error occurs.

Thank you,

Ed Blair