topic Re: ArcSDESQLExecute to delete sde.sde_versions table orphaned records in Data Management Questions

ArcSDESQLExecute to delete sde.sde_versions table orphaned records

vijaybadugu — Mon, 03 Jun 2024 14:30:47 GMT

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.

Re: ArcSDESQLExecute to delete sde.sde_versions table orphaned records

MarceloMarques — Mon, 03 Jun 2024 15:58:03 GMT

@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.

Re: ArcSDESQLExecute to delete sde.sde_versions table orphaned records

vijaybadugu — Mon, 03 Jun 2024 16:02:42 GMT

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

Re: ArcSDESQLExecute to delete sde.sde_versions table orphaned records

MarceloMarques — Mon, 03 Jun 2024 16:09:07 GMT

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.

Re: ArcSDESQLExecute to delete sde.sde_versions table orphaned records

vijaybadugu — Mon, 03 Jun 2024 16:11:26 GMT

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

Re: ArcSDESQLExecute to delete sde.sde_versions table orphaned records

MarceloMarques — Mon, 03 Jun 2024 16:16:22 GMT

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.

Re: ArcSDESQLExecute to delete sde.sde_versions table orphaned records

vijaybadugu — Mon, 03 Jun 2024 16:22:02 GMT

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

Re: ArcSDESQLExecute to delete sde.sde_versions table orphaned records

MarceloMarques — Mon, 03 Jun 2024 16:24:01 GMT

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.

Re: ArcSDESQLExecute to delete sde.sde_versions table orphaned records

vijaybadugu — Mon, 03 Jun 2024 16:29:56 GMT

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

Re: ArcSDESQLExecute to delete sde.sde_versions table orphaned records

MarceloMarques — Mon, 03 Jun 2024 16:40:46 GMT

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.

Re: ArcSDESQLExecute to delete sde.sde_versions table orphaned records

vijaybadugu — Mon, 03 Jun 2024 16:48:39 GMT

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

Re: ArcSDESQLExecute to delete sde.sde_versions table orphaned records

MarlonAmaya — Mon, 03 Jun 2024 18:14:10 GMT

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.htm#:~:text=When%20a%20read%2Donly%20ArcGIS%20Server%20feature%20service%20(only%20query%20and%20sync%20are%20enabled)%20contains%20versioned%20data%2C%20no%20replica%20version%20is%20created%20when%20you%20take%20the%20data%20offline.https://enterprise.arcgis.com/en/server/latest/publish-services/windows/prepare-data-for-offline-use.htm

Marlon

Re: ArcSDESQLExecute to delete sde.sde_versions table orphaned records

MarlonAmaya — Mon, 03 Jun 2024 18:22:30 GMT

@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

Re: ArcSDESQLExecute to delete sde.sde_versions table orphaned records

vijaybadugu — Mon, 03 Jun 2024 18:55:19 GMT

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.

Re: ArcSDESQLExecute to delete sde.sde_versions table orphaned records

vijaybadugu — Mon, 03 Jun 2024 18:51:34 GMT

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 ?

Re: ArcSDESQLExecute to delete sde.sde_versions table orphaned records

MarceloMarques — Mon, 03 Jun 2024 18:59:10 GMT

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

Re: ArcSDESQLExecute to delete sde.sde_versions table orphaned records

vijaybadugu — Mon, 03 Jun 2024 19:00:02 GMT

Thanks