ArcSDE Enterprise geodatabase compress error

Question

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.

Answer 1

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.

Answer 2

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?