Review branch recovery versions after upgrading an enterprise geodatabase

Summary

Workflows have been identified that can cause inconsistencies in the branch versioning system tables. When such an issue is encountered, features with missing branch versioning metadata can cause data inconsistencies when the version is reconciled, posted, and/or deleted.

The issue is a result of how the branch versioning metadata is maintained during save edits, reconcile and post. During these operations, branch versioning metadata may be removed from the geodatabase. When the branch version metadata is incorrectly removed, features may not correctly participate in future operations such as reconcile, post and delete version, resulting in inconsistent data. This issue occurred under race conditions where competing requests caused branch versioning metadata to be incorrectly removed for valid features.

Operations executing against incorrect branch versioning metadata could lead to:

• Branch versioned data referencing a version which has since been deleted, and,
• Branch versioned data referencing a moment before the common ancestor moment.

These system table inconsistencies are prevented and corrected in the following versions:

• ArcGIS Pro 3.3 and above
• ArcGIS Pro 3.2.4
• ArcGIS Pro 3.1.7
• ArcGIS Pro 2.9.13, and
• the ArcGIS Server Branch Versioning Data Consistency Patch

Upgrading an enterprise geodatabase will additionally check for any data inconsistencies in relation to branch versioned data. Inconsistencies are resolved during the upgrade by either creating new versions with repaired data, or by updating existing versions. These versions should be reviewed to determine if any of the data in the versions should be posted to Default.

This article describes the workflow to review inconsistencies resolved after a geodatabase upgrade. As noted above, the upgrade can repair the data in two fashions: creating a new recovery version or updating an existing version. For the purpose of this article, these versions will be referred to as new recovery versions and existing versions that have recovered rows, respectively. The geodatabase reviewed in the article has two versions that will be accessed and reviewed. The versions are named:

• RECOVERY_VERSION_BR_1 (new recovery version)
• EXISTING_VERSION (existing version that has recovered rows)

The workflow provided shows where the information about these versions can be gathered, how to connect to these versions, and what actions may be required against these versions in ArcGIS Pro.

The following is an outline of the workflow:

1. The Upgrade Geodatabase tool or the arcpy.UpgradeGDB_management function was used and reported a warning:

WARNING 003929: During the upgrade recovery versions (<value>) were created that require further review. Review <value> for more detail.

2. Review the sde_setup.log for a list of versions.
3. Prepare versions for access:
   1. Update the internal system branches table to include a service name.
   2. Ensure that feature services exist in your ArcGIS Enterprise portal with the datasets that contain edits for the versions.
4. Use the Differences view.
5. Edit/Reconcile/Post.
6. Delete versions.

Procedure

Review results from Upgrade Geodatabase tool and sde_setup.log

If inconsistencies are detected during the upgrade process, the Upgrade Geodatabase tool returns a warning stating how many versions were recovered as well as the location of the sde_setup.log file, as shown in the next image. Messages about geodatabase upgrades are written to the sde_setup.log file, which is created in the directory specified for your %TEMP% variable on the computer where the tool is run. The number of versions refers to both new recovery versions as well as existing versions that have recovered rows. The sde_setup.log file contains additional information about the versions that were repaired.

Note:
If using Python to upgrade the geodatabase, use the following example to ensure that warnings are properly returned to report recovery versions. If no warning is returned, no data inconsistencies were found in the geodatabase and no further action is required.

import arcpy
 
arcpy.UpgradeGDB_management(
    input_workspace=r"C:\temp\sysadmin_connection.sde",
    input_prerequisite_check="PREREQUISITE_CHECK",
    input_upgradegdb_check="UPGRADE"
    )
   
warnings = arcpy.GetMessages(1)
errors = arcpy.GetMessages(2)
 
if "WARNING 003929" in warnings:
    print(warnings)
elif "Recovery versions" in warnings:
    recovery_versions = warnings[warnings.find("Recovery versions"):warnings.find(")") + 1]
    
    for item in warnings.split("\n"):
        if "sde_setup.log" in item:
            logfile_loc = item.strip()
    
    print("--------------------------------------------------------------------------------------------------")
    print(f"WARNING 003929: During the upgrade {recovery_versions} were created that require further review.")
    print(f"Review {logfile_loc} for more detail.")
    print("--------------------------------------------------------------------------------------------------")
if errors:
    print(errors)

If necessary, download and open the example sde_setup_howto.log file in a text editor; sections of this file are referenced in the following steps.

1. Open the sde_setup.log file on the computer.
2. Review the sde_setup.log file for new recovery versions and existing versions that have recovered rows:
3. Search the sde_setup.log file for the section that states: Created recovery version(s).
   • The sde_setup.log file lists the newly created versions, having the naming convention, “sde.RECOVERY_VERSION_BR_<branch_id>”, also known as new recovery versions. This is shown in the following section of the log file:

[08:51:34.091] ==================================================================
[08:51:34.091] Begin recovery of branch versioned data associated with a missing branch...
[08:51:37.302] Created recovery version(s):
[08:51:37.302] sde.RECOVERY_VERSION_BR_1 with edits to:
[08:51:37.302]     - CREATOR1.POINT
[08:51:37.302]     - CREATOR1.LINE
[08:51:37.302]     - CREATOR1.POLYGON
[08:51:37.302] Completed recovery of branch versioned data associated with a missing branch.
[08:51:37.302] An administrator must update the service_name for the above versions in the sde.SDE_branches table in order for users to connect and review.
[08:51:37.303] ==================================================================

4. Note the datasets that have recovered data in the respective version(s), as edits in these versions will be viewed in the last steps in this article.
5. Search the sde_setup.log file for the section that states: Recovered rows in version(s). This is shown in the following section:

[08:51:37.304] Begin recovery of branch versioned data associated with an invalid common ancestor moment...
[08:51:37.622] Recovered rows in version(s):
[08:51:37.622] ADMIN.EXISTING_VERSION (service: Post_DataLoss) with edits to:
[08:51:37.622]     - CREATOR1.POINT
                           Object ID(s) : {46,436}
[08:51:37.622]     - CREATOR1.POLYGON
                           Object ID(s) : {462}
[08:51:37.622] Completed recovery of branch versioned data associated with an invalid common ancestor moment.
[08:51:37.622] A portal user must connect and review the above version(s).

• The sde_setup log file lists the existing versions that were updated, also known as existing versions that have recovered rows.
• Note the Object ID(s) for each dataset that has recovered data in the respective version(s), as these specific Object IDs will be viewed in later steps in this article.

Prepare recovery versions

There are a few required steps before the recovery versions can be reviewed. The following section describes the process to prepare both the new recovery versions and the existing versions that have recovered rows to be accessed and reviewed.

To prepare each new recovery version with the RECOVERY_VERSION_BR_<branch_id> naming convention:

1. Review the sde_setup.log file and inspect the list of dataset(s) for the corresponding version.
2. Ensure a service exists in your ArcGIS Enterprise portal with the corresponding datasets listed in the sde_setup.log file.

Note:
If a service does not exist in your portal with these datasets referenced, a new service must be published. Add these distinct datasets to a new map as the dataset owner, and publish a web feature layer with the version management capability enabled (Share branch versioned data).

3. Update the SDE_branches (or branches) system table with the corresponding service name identified in the previous step for each of the RECOVERY_VERSION_BR_<branch_id> versions using Structured Query Language (SQL) in an appropriate Database Management software solution.
   Use the following example, substituting the findings from your sde_setup.log file:

UPDATE sde.sde_branches
SET service_name = 'my_service_name'
WHERE name = 'RECOVERY_VERSION_BR_1';

4. To prepare each existing version that has recovered rows:
   1. Review the sde_setup.log and inspect the (Service : <service_name>) for the corresponding version.
   2. Ensure the service still exists in your ArcGIS Enterprise portal with the corresponding datasets listed in the sde_setup.log file.

Note:
If a service does not exist in your portal with these datasets referenced, a new service must be published. Add these distinct datasets to a new map as the dataset owner, and publish a web feature layer with the version management capability enabled (Share branch versioned data), using the same service name.

Access and review recovery versions

The versions are now prepared for consumption to review the repaired data.

1. Add a web feature layer to a new map in ArcGIS Pro from the services that contain new recovery versions and/or existing versions that have recovered rows.
2. Change to the version and open the Differences view, shown in the next image:
   • If the connected version is a new recovery version, all differences are repaired edits and need to be reviewed as noted in the next steps in this article (steps 3 and 4)
   • If the connected version is an existing version that has recovered rows, it may contain differences referencing Object ID(s) from existing edits, as well as differences referencing the Object ID(s) from the sde_setup.log file that were recovered during the upgrade. Although all differences should be reviewed, those recovered during the upgrade will need to be reviewed as noted in the next steps in this article (steps 3 and 4)

Note:
If a new recovery version only has edits to dataset system tables, for example: only Utility Network system tables, the Differences view may not display changes; therefore, the version can be deleted.

3. Review and inspect the edits. 
   1. Review user attributes and system-maintained attributes, such as editor tracking attributes.
   2. Review the Change Display for any shape column differences.
4. Based on the review in step 3 above, decide whether to keep the edits or not.
   • To keep the edits, reconcile and post the version to the default version.
     • If only a subset of edits is desired, make the appropriate edits prior to the reconcile/post operations.
   • To discard the edits:
     • For new recovery versions, the version can be deleted.
     • For existing versions that have recovered rows, edit the associated Object ID(s) to have the same attributes and shape as the Default representation. Otherwise, the version can be deleted and the work prior to the repair can be recompleted in another version.