Keep an eye on your Versions table after dropping replications

1278
15
10-07-2016 08:13 AM
Highlighted
Occasional Contributor II

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.

15 Replies
Highlighted
Occasional Contributor III

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?

tim

Reply
0 Kudos
Highlighted
Occasional Contributor II

First time was with a 10.1 geodatabase, this time was after upgrading it to 10.3.1. MSSQL 2008 R2.

Reply
0 Kudos
Highlighted
Esri Frequent Contributor

I assume you're synchronizing between the replicas before compressing the geodatabase?  Replication and Archiving will prevent a Full Compress certainly.

Reply
0 Kudos
Highlighted
Occasional Contributor II

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.

Reply
0 Kudos
Highlighted
Esri Frequent Contributor

Gotcha.  So did you unregister using the Replica Manager on both the parent and child replica sides?

Reply
0 Kudos
Highlighted
Occasional Contributor II

Yes. Both were one way and they were removed from the child databases fine.

Reply
0 Kudos
Highlighted
Esri Frequent Contributor

Hmmm...is it the hidden synchronization versions that are pinning states then?

Reply
0 Kudos
Highlighted
Occasional Contributor II

Thats what I assume. Once those two rows were deleted the two corresponding states in SDE_States compressed away and everything went back to normal.

Reply
0 Kudos
Highlighted
Esri Frequent Contributor

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

Good luck!

Reply
0 Kudos