Issue with distributed collaboration SDE_versions and SDE Compression.

[twangtx]

Hello,

The TLDR is, we have a distributed collaboration that makes copies from out EGDB to our AGO and we're having issues with the database not fully compressing after doing bulk edits.

Environment:

• ArcGIS Enterprise: 11.2
• Enterprise Geodatabase: 10.9.1(SQL Server), with a dedicated feature data set that contain feature classes that we bulk reload (delete features and append) data. It's Traditional versioned without move edits to base) and has GlobalIDs. Just simple features: points, lines, and polygons.
• We're publishing the services to Portal with Feature Access turned on and Editing turned off. And the only operations allowed are Query and Sync.
• We have also set up a headless DB user account for publishing/sharing the data.

The problem:

We're testing out sharing data through the Distributed Collaboration to our ArcGIS Online and we've been running this for a week or two now. And I'm noticing that after we do a bulk reload of the feature classes we use for the distributed collaboration, then run a reconcile and compress script, the SDE_States aren't zero stating, and now we have 6 states and 3 of them were somehow produced (based on the time stamp) after we ran reconcile and compress. The SDE_versions table, I noticed, also has a growing list of Sync_Send and Sync_Receive, which share the same state_id as the states that aren't zeroing.

A few things I've tried but with no success.

1. Manually kicked off the reconcile and compress. Which blocks/disconnects all users, reconciles and compresses, then allows connections again.
2. Turned off the published services and re-ran.
3. Unshared from the Distributed collab group, deleted the services from ArcGIS Server, and removed the feature service replicas (using Pro) and we have no geodatabase replicas. Then re-ran reconcile and compress.
4. Latest attempt, tried unversioning the feature data set, which actually popup a message saying 4 of the feature classes were uncompressed and if I wanted to compress them before unversioning (I did).

I'm not a DBA by any stretch of the imagination, just knowledgeable enough to poke and prod and ask questions. So I'm not sure what to do at this point. We'd like to move this into production, but I'm hesitant to move forward if this is going to be an issue.

Thank you!

[BiscuitsAndGravey]

Hey, I was wondering if you ever heard anything back on this?

[twangtx]

Unfortunately, not, I put in a ticket with ESRI Tech support, but all they were able to do was walk me through the process to manually remove the replicas (in SQL Server Manager) so we could perform a full compression.

It seems like things that are supposed to happen, to clean up orphan versions and whatnot, aren't happening with normal routine maintenance. I came across some ESRI documentation (for ArcMap) regarding reconciling and posting for sync enabled data, which I believe Distributed collaboration data would be, Automate reconcile and post operations for sync-enabled data—ArcMap | Documentation, there was a blog too I found but I can't find it.

We haven't looked further into this, for the time being we're going to stick with python scripts to overwrite our hosted feature layers in AGO.

Here are some of the follow up questions I asked, if it would help you.

1. If we’re using the collaboration to regularly copy data to ArcGIS Online, should those SYNC versions disappear automatically when we run the reconcile, post, and compress?
   
   >>Yes, SYNC versions should typically disappear after running the reconcile, post, and compress operations. This is because these operations are designed to integrate changes and clean up versions that are no longer needed.
   
   >>In case the SYNC versions do not disappear, it may indicate that there are pending changes or conflicts that need to be resolved before the versions can be cleaned up.
2. What would cause the orphaned replicas?
   >>Orphaned replicas can occur due to several reasons:
   >Network issues during synchronization that prevent updates from being completed.
   >Manual deletions of replicas without properly unregistering them or else change in the contents of the collaboration.
   >Configuration errors in the collaboration setup that lead to inconsistencies.
3. And if we are using the collaboration, would it be normal to not get a full database compression?
   
   >>It is not uncommon to experience incomplete database compression when using collaboration. This can happen if there are active replicas or if the database is under heavy load, preventing the compression process from completing successfully.
   
   
4. Another thing I noticed when trying to figure out the compression issue, whenever I did a bulk delete features and append to the feature class in the database, the new rows would appear in the feature layers in Portal and in the AGO copy (from the collaboration) but would not show up in SQL Server Manager. I know about the delta tables, which led me to the compression issues, do you know why the rows would show up in the feature service but not on the SQL Server side?
   
   >>When you perform a bulk delete and append operation, the new rows may appear in the feature layers in Portal and ArcGIS Online due to the way the feature service handles updates.
   >The delta tables are used to track changes, and these changes may be reflected in the feature service before they are fully committed to the SQL Server database.
   
   If the new rows do not show up in SQL Server Manager, it could be due to:
   
   >>The changes being held in the delta tables until a full geodatabase compress operation is performed.
   >>A delay in the synchronization process that has not yet pushed the changes to the main tables in SQL Server