nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Find more posts tagged with

Geodatabase

Geoprocessing

Python

Accepted answers

All comments

MMBR

@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

MMBR

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

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

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

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.

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 ?

MMBR

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

vijaybadugu

Thanks

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.