topic Re: Keep an eye on your Versions table after dropping replications in Geodatabase Questions

Keep an eye on your Versions table after dropping replications

ChrisMathers1 — Fri, 07 Oct 2016 15:13:23 GMT

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.

Re: Keep an eye on your Versions table after dropping replications

TimMinter — Fri, 07 Oct 2016 16:01:32 GMT

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

Re: Keep an eye on your Versions table after dropping replications

Robert_LeClair — Fri, 07 Oct 2016 16:30:04 GMT

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

Re: Keep an eye on your Versions table after dropping replications

ChrisMathers1 — Fri, 07 Oct 2016 17:02:12 GMT

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

Re: Keep an eye on your Versions table after dropping replications

ChrisMathers1 — Fri, 07 Oct 2016 17:06:00 GMT

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.

Re: Keep an eye on your Versions table after dropping replications

Robert_LeClair — Fri, 07 Oct 2016 17:07:41 GMT

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

Re: Keep an eye on your Versions table after dropping replications

ChrisMathers1 — Fri, 07 Oct 2016 17:19:33 GMT

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

Re: Keep an eye on your Versions table after dropping replications

Robert_LeClair — Fri, 07 Oct 2016 17:20:35 GMT

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

Re: Keep an eye on your Versions table after dropping replications

ChrisMathers1 — Fri, 07 Oct 2016 17:31:22 GMT

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.

Re: Keep an eye on your Versions table after dropping replications

Robert_LeClair — Fri, 07 Oct 2016 19:29:47 GMT

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!

Re: Keep an eye on your Versions table after dropping replications

ChrisMathers1 — Mon, 10 Oct 2016 14:20:25 GMT

The compression that runs at midnight each night is a script that kicks all users, blocks new connections, and syncs before compressing. Does deleting those hidden sync versions not affect the replications? I have 7 replications currently off this database so the version table is holding onto 7 SYNC_SEND versions and states. I say 7 states but in reality all 7 have the same state ID.

Re: Keep an eye on your Versions table after dropping replications

Robert_LeClair — Tue, 11 Oct 2016 15:11:31 GMT

Best workflow to remove hidden synchronization versions is to synchronize back to the data receiver replica, resolve any conflicts (if any), unregister replicas (which will delete hidden sync versions), rep/post/rec to DEFAULT, and compress. From there, you can rebuild replica workflow. Not required certainly but you could do this if you think there is state pinning going on.

Re: Keep an eye on your Versions table after dropping replications

TamiOnstad — Tue, 18 Oct 2016 16:03:56 GMT

I am dealing with a somewhat similar problem, but unfortunately I did NOT sync before rec/posting all of our versions back to Default and running compress. This is a development environment so not fatal, but how do you recover from a state where you have deleted the sync versions and you now have a parent and child database without in-sync data? I assumed I could just unregister the replica (had to since I had somehow blown away the replica versions) and create a new replica. The globalid values still match between the databases, so I created the replica with existing data option. It syncs, but the changes are not being propagated to the child database.

Any thoughts? There has to be a way to recover from something like this without recopying all the data back over to the child database, which could take a day or more.

Tami O.

Re: Keep an eye on your Versions table after dropping replications

Robert_LeClair — Wed, 19 Oct 2016 21:40:44 GMT

Hi Tami - when you unregister the replica pairs, it removes the connection between the parent/child geodatabases. Further, the hidden synchronization versions that are in the parent/child geodatabases are used to compare what's been added, updated, and/or deleted are also deleted. The only way I can think of getting those lost edits back would be from an incremental backup of the parent/child geodatabase. Could be wrong but that's my guess.

Re: Keep an eye on your Versions table after dropping replications

TamiOnstad — Wed, 19 Oct 2016 21:52:00 GMT

Thanks Robert, that's what I was afraid of. I think the archiving option might be safer for us since our version and rec/post/compress is so customized.

Appreciate your input!

Tami O.

Re: Keep an eye on your Versions table after dropping replications

Robert_LeClair — Wed, 19 Oct 2016 21:55:47 GMT

You could use the Archive Option on One-Way replication via the DEFAULT version to decouple versioning from replication but this a pretty niche workflow. Good luck!