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. 

[MarceloMarques]

@vijaybadugu - you cannot delete records from the sde.sde_versions table directly because you will corrupt your geodatabase, a traditional version in the geodatabase is not just a record in the sde.sde_versions table, there are many other tables in the sde repository and the data that reference the version, to delete versions use the GP Tool Delete Version.

You can call the GP Tools from an arcpy python script.

1. GP Tool Unregister Replica.
Unregister Replica (Data Management)—ArcGIS Pro | Documentation

2. GP Tool Delete Version.

Delete Version (Data Management)—ArcGIS Pro | Documentation

3. After the versions are deleted, run the GP Tool Compress.

Compress (Data Management)—ArcGIS Pro | Documentation

I hope this helps.

[vijaybadugu]

I tried delete version tool to remove the orphaned versions . It did not delete the orphaned records . It deletes only visible versions not hidden ones . I have no clue on this . I have been deleting records from sde_versions as suggested by esri technical team using delete sql statement using sql server .  now , I decided to automate the process

[MarceloMarques]

It surprises me that Esri Technical Support suggested to delete records directly in the sde.sde_versions table, because is well known that any attempt to make changes directly in the arcsde repository tables can corrupt the geodatabase, I believe Esri Support should have diagnosed your workflow differently and perhaps logged a bug or asked for enhancement request if a solution could not be found using arcpy python scripts and GP tools.

[vijaybadugu]

Ok. I am able to unregister the replicas and I could not delete hidden records with delete version tool. Could you please suggest any other solution 

[MarceloMarques]

This sounds like a bug to me, as I never encountered issues, I was always able to unregister the replicas and delete the versions that were associated with the replicas. You might check if all users and services are disconnected prior to attempt to do these tasks.

On the other hand, if the issue still persists then you will need to work with Esri Support and they will need a copy of your enterprise geodatabase to diagnose the problem, and try to reproduce the issue, then Esri Support can try to find a workaround and/or log a new bug and/or request an enhancement request.

[vijaybadugu]

I will try again using your suggestion before going to the support team. How to delete hidden SYNC versions associated with replica? 

[MarceloMarques]

I connect with the sde user and I am able to see all the versions, then I am able to unregister the replicas, and then delete the versions that were associated with the replicas.

[vijaybadugu]

When i see in SQL Server sde_versions table , 

at the same time, I logged in as a SA user, 

I could not see any versions 

[MarceloMarques]

If you logged as SA user, then this is the SQL Server Instance System Administrator, the SQL Server Geodatabase is not designed to work with the SA user.

You must use ArcGIS to connect to the SQL Server Geodatabase using the SDE user, the owner of the ArcSDE Repository, which will be able to see all the versions and delete any versions, if you connect with the data owner user, then you will still see all versions but the data owner user will be able to delete only the versions that the data owner user created.

You will not be able to delete a version if there are other users connected that might be using that version, hence make sure you disconnect all users and stop all ArcGIS Server services, then you can perform the unregister replica, and delete the versions associated with the replica, but you need to connect with the SDE user.