I have had consistent problems pushing edits from the ADD/DELETES tables to the base database. I am running ArcGIS Server Enterprise Version 10.0 on Oracle10g.
Set-up: SDE.DEFAULT with a QA/QC version formed from DEFAULT, then several children versions formed from the QA/QC version for data editing. Several times a week, the children are reconciled and posted to QA/QC, then QA/QC is reconciled and posted to DEFAULT. I then (1) make sure nobody is connected to any of the versions, (2) run ANALYZE, (3) run COMPRESS, (4) run ANALYZE. The compress log states that it ran properly, but rarely do all edits in the ADD/DELETES post to base. The number of records in the ADD/DELETS grows significantly over time. I have verified that many edits are �??stuck�?? in the ADD/DELETES and will not push to DEFAULT. They seem stuck in limbo�?� At this point, nothing will push to base and there are 10�??s of thousands of edits stuck (I have run COMPRESS several times at this point). If I delete the versions and run COMPRESS, SDE thinks that all the edits are orphans and it simply deletes everything in the ADD/DELETES (ESRI gave me that advice the first time around and I lost my edits, now the issue is occuring again).
I have run the SDEGDBREPAIR commands and the tool verifies that there are no orphaned records, no duplicates, no missing or redundant rows and that all versions, states, and lineages are valid.
What do I do to get these ADD/DELETE edits pushed to base?? Anyone else have this issue?
An edit can only be compressed to the base tables if ALL versions have that edit in common (and there are no locks). This means that a single un-reconciled version can pin your edits like you are currently seeing. This also means that your nightly process of rec/post to the QA version followed by rec/post to default needs one more reconcile of all versions a second time at the very end to get everybody "equal".
Do you have any GDB replicas or disconnected editing versions? Perhaps private versions exist that you are unaware of?
There is a free ESRI tool called Geodatabase Toolset (GDBT) that will allow you to visualize your state tree and possibly identify your offending version(s). You could also figure out the offending versions via straight sql if you were so inclined, and the output would be a nice report for you to monitor on a daily basis. I do not have the sql to list here, however I'm sure you could find it if you scour around.