ArcSDESQLExecute to delete sde.sde_versions table orphaned records

[vijaybadugu]

I have already created few read-only offline maps for just read only purpose. When we create a offline maps, it does create replications in geodatabase. when users download the offline maps from devices, it does create a replica and few orphaned records got inserted in sde.sde_versions  table. the records are not being deleted even though we unregistered the replicas and deleted offline maps from the devices. in order to compress the database, we should delete orphaned records and unregister the replicas. I have already written a python job to automate this process.  I am able to unregister the replicas successfully without any issue, the problem is with deleting records from sde_versions table,

sde_conn = arcpy.ArcSDESQLExecute(gdb_conn)
sql_query = "DELETE FROM sde.sde_versions WHERE name LIKE 'SYNC_%'"
sde_conn.execute(sql_query)

even though the statement executed successfully, the records are still exists in the table. Please suggest on this to proceed further. 

[vijaybadugu]

Although, I Connected with sde user , could not see those SYNC versions 

[MarlonAmaya]

Hi @vijaybadugu ,

Before we delete any SYNC version we have to make sure we confirm these are in fact orphaned to avoid any potential issues. Additionally, I would like to know why your sync versions are not being deleted even with unregistering the replicas.

How are you conducting the unregistering of replicas? Pro? Deletion of offline map?

You mention read only offline maps - are you indicating that the service is published with Query and SYNC only to support non editing desire in offline workflow?  Read-Only services do not create replica versions.

https://enterprise.arcgis.com/en/server/latest/publish-services/windows/prepare-data-for-offline-use...

Marlon

[vijaybadugu]

It is READ-ONLY with Query and Sync enabled feature service. Edits are being updated on daily basis at back office using pro. and to get the latest edits, field users must update the offline maps to sync with server data. 

[MarlonAmaya]

@vijaybadugu 

My apologies as I did not see previous post from Marcelo.

You will not see any SYNC versions using Pro GUI. These versions will not show. Expected behavior. 

As Marcelo stated, these versions should not be deleted until further investigation and confirmed that they can be deleted. Prior to deletion of any of these SYNC versions, take a backup. These version will not appear in drop down using Delete Version GP tool, but the name can be typed in and will be deleted if conducted by the version owner. 

Marlon

[vijaybadugu]

In order to automate the process, I have to use ArcSDESQLExecute to access sde.sde_versions table for owner and version name, then use  Delete Version tool to delete orphaned versions. Can I proceed with this approach ?

[MarceloMarques]

I believe you can, that seems a feasible alternative, hope the Delete Version tool will remove those versions properly.

[vijaybadugu]

Thanks