My organisation has ArcSDE enterprise geodatabase(ArcGIS 9.2) with MS SQL Server 2008 as DBMS.

When I compress the geodatabase I am getting the follwoing error

 Failed to compress the database.

 Underlying DBMS error [microsoft][ODBC Driver11 for SQL Server][SQL 
 Server]Violation of PRIMARY KEY constraint'Value'. cannot insert dulpicate 
 key in object'Value']

I ran the Diagnose versions table and then repair versions table but I still get the same compress error after executing repair.

  • Welcome to GIS SE. As a new user, please take the Tour. You haven't provided any indication of the release of ArcGIS involved, but it has to be quite old, since SQL Server 2008 hasn't been supported in a long time. Please Edit the question to add details. It's unlikely we'll be able to help, due to the fine level of detail needed.
    – Vince
    Jan 2, 2019 at 13:33
  • Find your Duplicate ID (on your primary key field) stackoverflow.com/questions/2594829/…
    – Mapperz
    Jan 19, 2020 at 22:48

2 Answers 2

Underlying DBMS error

...means that your SQL Server DBMS is returning the error, not the compress function.

Violation of PRIMARY KEY constraint'Value'. cannot insert duplicate key in object'Value'

...indicates that you're trying to insert a duplicate primary key into some table.

What's likely happening is you have a version out that contains a 'new' feature that has the same primary key value as an exiting feature in the base table. When you try to compress, the 'new' feature is being moved into the base table and a conflict arises when SQL Server detects the duplicate primary key value.

Since you've already run the Diagnose/Repair Versions tools, try to reconcile and post, in order, your existing versions. Delete any versions that you do not need to persist.

  • Unfortunately, that error could also mean the metadata is corrupted, or that an an iN table has overflowed (or been reset) or some database maintenance has gone awry.
    – Vince
    Jan 2, 2019 at 14:46
  • thank you @Radar. i donot have any versions as i deleted all the versions and disconnedted all users to achieve full compression. do you have any other solutions i can try, thank you .@ Vince.. what do you suggest i can do to resolve the issue?
    – tsering
    Jan 4, 2019 at 4:06

It could be due to the orphan state_id or state_ids. Can you answer and perform the following:

  1. Did sde Diagnosis command return any problematic states?
  2. Run the following command and check if there is any state which has not been closed:
    select state_id,creation_time,closing_time from sde_states;
  3. Have you checked the direct connection log file, was there any state_id mentioned along with the Violation of PRIMARY KEY constraint'Value' error?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.