For the second time when investigating why our primary production database is slow I found that the edit tables are not compressing. The version tree from GDBTools is showing a pretty good compression each time but those edits still remain. It turns out that once again when I removed two replications they are taken out of all database tables except SDE_Versions. These two versions are acting as blocking states keeping 2 months worth of edits from compressing. The only way I've found to fix this is to back up the database and delete the rows from the table directly. I deleted them in descending order of creation date compressing between deletes. After the last compress all the edit tables were clean and empty and the database had updated all the state lineage numbers automatically. No edits were lost as far as we have been able to tell. I don't know if its just our database but DBA's may want to keep this in mind.
Thanks for sharing your observation Chris Mathers. I've noticed some potentially similar oddities in our Production and Publication pattern distributed geodatabase configuration and will have a more detailed look at it with your info in mind.
We're using ArcGIS 10.3.1 with some patches and SQL Server 2012 (11.0.6523). Which ArcGIS version and database are you using?
Yes we replicate before compressing. In this case the replicas are no longer registered but the entry for them was not dropped from SDE_Versions when they were removed. The compress couldn't get past those two states and the versions couldn't be replicated because they did not exist anymore. The entry for them was not in GDB_Items anymore, just SDE_Versions.
An older workflow (SDE Command Line) for a Full Compress and replication but it documents well. "Below are the steps for a successful compress: - Take a backup of the data. - It is important to have no locks on the data. Disconnect all the Users.* - Synchronize all the Replicas without making any edits. - If there remain any Sync Versions in the state tree after the resynch process, delete these Sync Versions using the Sdeversion -o delete option. - Reconcile/Post the data. - Compress the database twice."