Unable to compress Enterprise GDB to a single state

[wilcow]

ArcGIS Server 10.6.1

We have a table that's shared with another department who uses calculations in the table for customer billing. Lately They've been complaining that they are unable to see changes we make to the table in GIS when they view the table in MSSQL. 

I'm thinking the edits were stuck in an older state, so I ran Compress and had them re-check and the changes were still not visible. 

I dove in and checked SDE_compress_log and saw that after the last compress, end_state_count = 2

I have run the Compress tool 20+ times trying to get to a single state but to no avail. I've stopped all ArcGIS Server services and can verify that there are no connections in Pro; I even tried to delete the record directly in SQL, but still can't get that last state to compress.

I know it can be done - I can scroll back through the compress log and see us at state count = 1 in the past, but for some reason it just won't take this time.

Any advice on shaking this loose? Am I missing something obvious? TIA for any thoughts on the matter.

[George_Thompson]

Do you have another version that has not been reconciled / posted?

Here is a blog that may help: https://community.esri.com/t5/esri-technical-support-blog/tips-for-compressing-with-existing-geodata...

[wilcow]

@George_Thompson nosir, I should've mentioned that I rec & post & delete all child versions prior to compression

thanks for the link - I'll read through and see if anything sheds some light on this

[MarceloMarques]

@wilcow - Let me try to explain.

Geodatabase Traditional Versioning:

You have child versions of sde.default that you reconcile and post to sde.default, then via ArcGIS Pro you can see that the edits are now present in the sde.default, but if you look the featureclass or table directly in the database you might not see that the edits are there, then you decide to run a sde compress, this shall then move the rows from the delta tables ( Adds Table and Deletes Table ) to the base table (traditional versioning), in a normal situation this is what will happen, but there are many factors that can make the data to remain in the delta tables, for example, it might have child version of sde.default that are pinning the state lineage version tree, hence the sde compress cannot move the rows, the sde compress will still trim the state lineage tree, but only after those child versions are reconciled, posted and deleted that the sde compress will be able to move the rows from the delta tables to the base tables.

This situation and others are well explained in the training classes below.

Implementing Versioned Workflows in a Multiuser Geodatabase | Esri Training Instructor-Led Course

Deploying and Maintaining a Multiuser Geodatabase | Esri Training Instructor-Led Course

You might never be able to achieve state 0 with sde compress if you need to keep child versions of sde.default, the only way is to reconcile, post and delete all child versions and then run sde compress multiple times. And after that you need to gather new statistics and rebuild indexes (attribute indexes and spatial indexes) and gather new statistics again.

Geodatabase SQL Access:

Now, if you have an external application that needs to access the data, then that application can use the multi-versioned views to query the geodatabase data in the sde.default version.

For more information see the documentation below.

SQL access to enterprise geodatabase data—ArcMap | Documentation (arcgis.com)

ArcGIS Compatibility:
About ArcGIS Server 10.6.1 & ArcGIS Pro & Geodatabase ArcSDE Repository Compatibility.

Client and geodatabase compatibility—ArcGIS Pro | Documentation

Client and geodatabase compatibility—ArcGIS Server | Documentation for ArcGIS Enterprise

• You do not have to keep your geodatabase and ArcGIS clients at the same release, but it is recommended that you do so. Geodatabases and client software are designed to work together, and if you let one get too many releases away from the other, you risk encountering problems or unexpected behavior.
• This is especially true when you use a mix of client versions at your site. A newer client can create newer dataset types in the geodatabase that older clients cannot access. For enterprise geodatabases, waiting too long between geodatabase upgrades may mean you have to upgrade the underlying database more than once before you can upgrade the geodatabase.

ArcMap 10.6.1 & ArcGIS Server 10.6.1 are retired as January 1, 2024.

| Version | Release date | General Availability | Extended | Mature | Retired |
ArcMap Life Cycle | Esri Support

10.6.1

July 17, 2018

Jul 2018–Dec 2019

Jan 2020–Dec 2021

Jan2022–Dec 2023

January 1, 2024

ArcGIS Enterprise Life Cycle | Esri Support

10.6.1

July 17, 2018

Jul 2018–Dec 2019

Jan 2020–Dec 2021

Jan 2022–Dec 2023

January 1, 2024

• IMPORTANT NOTE: ArcGIS 10.8.2 is the current release of ArcMap and will continue to be supported until March 01, 2026. We do not have plans to release an ArcMap 10.9.x. This means the 10.8.x series will be the final release series of ArcMap and will be supported until March 01, 2026.

REMARKS:
If you want to use ArcGIS Server with ArcGIS Pro then the advice is to move the deployment to a more recent release, it might be difficult to upgrade your current environment, and it might be easier to create new servers, deploy and configure the new ArcGIS software and republish the ArcGIS Server Services.

• ArcGIS Pro 3.2.2
• ArcGIS Server 11.2
• SQL Server 2022 + Latest Cumulative Security Update
• ArcSDE Geodatabase Repository 11.2.0.3.2
  
  
• FAQ: What SQL Server Database Versions Are Supported by ArcGIS Enterprise and ArcGIS Pro? (esri.com)
• ArcGIS 11.2 and ArcGIS Pro 3.2 requirements for Microsoft SQL Server—ArcGIS Enterprise | Documentati...
• ArcGIS Pro Life Cycle | Esri Support
  

I hope this helps.

[wilcow]

@MarceloMarques Thanks for the reply!

I should have made clear in the original post that before compressing, I Rec & Post all child versions and delete so sde.DEFAULT is all we had at the time.

Much obliged for the links and info - I'll look into the use of multiversioned views to provide outside access to the table - but please note that ths table is part of a workflow that involves an interface with a separate system - they update fields with data from our ERP system and the main issue is that the interface is unable to find new records to add data.

[MarceloMarques]

@wilcow - if the table is registered with the geodatabase and the table has geodatabase traditional versioning enabled then the external application needs to use the geodatabase multi-versioned view of that table to query and edit the data in the geodatabase sde.default version, you can find more details on how to edit the data via the multi-versioned view in the documentation SQL access to enterprise geodatabase data—ArcMap | Documentation (arcgis.com)

[MarceloMarques]

@wilcow - if you reconciled, posted all child versions of sde.default and you deleted all child versions of sde.default, and executed the sde compress multiple times and you still cannot see all the data in the base table and you looked into the the delta tables and you still see the data there, then you might have an issue.
If your SQL Server ArcSDE Geodatabase Repository is 10.6.1 and you are trying to run the sde compress with ArcGIS Pro 3.x then that might be the problem and you might want to try with ArcCatalog 10.8.2.

You can also execute these 2 GP tools to see if they can find and fix any traditional version issues.

ArcToolbox > Data Management Tools > Geodatabase Administration

Repair Version Metadata

Repair Version Tables

These tools are available in ArcCatalog 10.8.2 and in ArcGIS Pro.

If you are still not able to achieve state 0 sde compress then I will ask you to open a ticket with Esri Technical Support to investigate further the problem.

I hope this helps you.

[MarceloMarques]

@wilcow - it just occurred to me that if you have ArcGIS Server Services that are connected to the SQL Server ArcSDE Geodatabase and/or other users connected as well via ArcMap / ArcCatalog / ArcGIS Pro, then this might also prevent you from achieving the sde compress state 0, this because these other connections create geodatabase locks and rdbms locks, hence you will need to stop all ArcGIS Server Services or just stop ArcGIS Server all together and ask all users to close ArcMap / ArcCatalog / ArcGIS Pro, then you will connect as the sde user, make sure you do not click any other geodatabase connection file, and execute the sde compress a few times, this shall make possible to reach sde compress state 0 and all rows in the delta tables shall move to the base tables.

Note, right after that gather new statistics, rebuild indexes (attribute indexes and spatial indexes) then gather statistics one last time, and take a full database backup.