sde duplicate geometry being created (error cannot insert duplicate key on reconcile)

[DavidJenkins]

I am managing a production workflow with about a half-dozen editors in a versioned environment set up against a single ArcSDE instance (SQL Server 2008).  Every once in a while something will happen where the editors will begin getting errors on reconcile about duplicate keys.  Upon looking into the issue, I discovered that though there are two physical records in the geometry (duplicates) but only one record in the business table and the system will not allow a delete of the object(s).  My initial thought was "multi-part feature" so explode, of course the result was "no multipart features found...".

Since it would not let the editor reconcile, I have been able to delete the affected version and recreate it minimizing the losses but it's still a loss...

Here's the thing.  I am using Excel to track and report editing progress.  I do this by executing sql queries (built and tested in SQL Server) through Excel data connections to read the contents into a worksheet, pivot the results and create graphs and charts.  This is nothing new.  I've been doing this for years and have never had a problem before.  However, I've surmised that the error happens when there are active editing sessions and I run the reports.

I have therefore suspended the use of tracking progress using Excel but now I am faced with reporting progress some other way.

If anyone understands what may be happening here I would appreciate some insight so I can plan a new path for reporting.

Thank you,

Dave Jenkins

[nicogis]

are you installed available patches for sql server and arcgis?

[DavidJenkins]

are you installed available patches for sql server and arcgis?

Yes, I'm somewhat a stickler for such things.  I try to pushed forward as much as possible with all patches unless there's a problem.

Since my last post I've completely disassociated my reports from the database but we occasionally have the same issue.  The editors will be working when all of the sudden one of them will have the error "cannot insert duplicate key" upon reconcile.  Then another one will get it and another... and so on.  Upon monitoring the issue more closely we're closing in on the fact that the issues seem to occur "around" the same time of day.  I'm working with our IT department to see if they're performing any maintenance during these times but it seems unlikely.  I'm wondering if it may be related to communication disruptions with the server.

Dave Jenkins

[TerryAdams]

David,

Saw your post.

We are having exactly the same issue and cannot figure out why it is happening.  Same thing as you report, every now and again we're getting duplicate primary key errors when GIS officers attempt to reconcile.  ESRI support have thus far been stumped as well.  We can go for months without issue - then boom, we cannot get rid of the issue no matter what we try.

Did you have any luck at all in figuring this out?

[AsrujitSengupta]

did "sdegdbrepair -o daignose_table" report any inconsistency?

[TerryAdams]

Unsure of the command used I admit (not done by me), but yes, after the last reconcile / post we have duplicates reported in each of the editors' (3 of) versions when we run a command line check.  We do have a command line repair tool ESRI alerted us to which fixes the problem.  However, this requires all users to be disconnected from the system which has implications in our environment.

To perhaps make the situation more clear.

Editor 1 reconcile/post with admin version

Editor 2 reconcile/post with admin version

Editor 3 reconcile/post with admin version

Editor 2 reconcile with admin version

Editor 1 reconcile with admin version

This is to get all 3 editors versions the same.

Then....

Admin Reconcile / Post with SDE default.

Admin compress the database.

If editor1, 2 or 3 then tries to reconcile again....... duplicate primary Key is reported.  This would - upon some research - appear to be a standard SQL duplicate primary key error.

Now.....

Load in the suspect dataset into arcmap.

open the attribute table.

What you find is that when you select one of the 'duplicated' features,the attribute table will report 2 selected features, I assume because there are two entries in the database using the same primary key. You can also try highlighting one feature from the attribute table and you will also get two features being reported as selected.

Anyone who's had this happen and can share info about it we'd like to hear from you.  We are stumped.

[AsrujitSengupta]

Run the "sdegdbrepair -o daignose_table" and report the output here. it may provide a better insight.

ArcSDE Administration Command Reference‌

Examples:

SQL Server:  sdegdbrepair -o diagnose_tables -d SQLSERVER -V ALL -s sqlserver_instance_name -p sa -u sa_password -D test -i sde:sqlserver:sqlserver_instance_name

Oracle:  sdegdbrepair -o diagnose_tables -d ORACLE11G -V ALL -u sde -p sde_password@oracle_netservice_name -s server_name

[TerryAdams]

Asrujit - thanks for your help. Appreciated!

Output from the command is as follows.  This *was* the command we were using as it turns out.

***

402 duplicate rows found in
ARCSDE.GISDBA.COB_HOUSE_NOS

   Versions:

    
SPATIALOFFICER1.SpatialOfficer1

Diagnose Tables: 235
multiversioned tables examined,

               
1 multiversioned tables had orphaned, duplicate, missing or redundant rows.

[AsrujitSengupta]

So as the output indicates,there are duplicate entries present in the geodatabase which needs to be taken care off.

Now take a full database backup and then run the "sdegdrepair -o repair_tables" command to remove these duplicate records. Make sure that nobody is connected to the geodatabase and working on it when you execute this command.

[AsrujitSengupta]

This will take care of the errors for now, however if this is a regular/frequent observation, I would suggest looking into your workflow once more. Maybe discussing with ESRI Tech-Support will give you a better idea.

Also is all the editing done using ArcGIS applications? Or 3rd Party Applications are used to edit\update the data as well?