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.
