The Unorthodox Way to Compress a Replication-Enabled Versioned GeoDatabase to State 0

If you landed here chances you lost hope of all the links and by-the-book ways to compress your geodatabase. You probably have a versioned geodatabase that no matter how many times you compress, no matter how many users you kick out, how many connections you kill; The state is not set to zero.

You have one version SDE.DEFAULT and it is still pointing to state 8549347 or whatever and this state is referencing thousand of edits. So you are stuck with a huge performance issue on your database because of all the queries to the A and D table. Even if you reconcile and post all versions you still can't seem to get to state 0. Usually replication might cause this, so you might have few hidden versions pointing to state 0 . Therefore compress command will get confused when these versions with state 0.


BEFORE YOU DO THIS, BACKUP YOUR DATABASE, DO AN ORACLE DUMP OR A SQL SERVER BACKUP. ALTHOUGH THIS THING WORKED FOR ME THREE TIMES I CAN'T GUARANTEE JACK.


Buckle up, we are about to open the hood of the SDE Technology and loose few bolts in the SDE engine, change a fuse or two, start the engine manually and put the hood back.


1- Fireup your DBMS, connect as SDE user.

2- Open the SDE.VERSIONS

3- You may skip this to 4 if you just want to do the fix, else continue reading if you want to know how this works

Name: The Version Name
Owner: Who created the version
Version_ID: An ID for the version
Status: A status that tells whether this version if private, public, protected or even system
State_ID: To what state this version is pointing, the state
Description: Text, description of the version
Parent_Name: This is a stupid field, bad ERM design, there is a parent_version_id so we can get the name of the parent. Unless they did it like to avoid joining, thus speed up the query response time, then they are smart and I am sorry.
Parent_Owner: again, stupid field, never mind.
Parent_Version_ID: The parent version id there you go.
creation_time: when did this version is created

4- Now you might have different values, but look at the whole structure it should be the same

5- The geodatabase is not compressing because there are two states pointing to zero which makes the compress command thinks everything is rosy. So take your screwdriver lets screw this DB.

6- We will do a manual reconcile to those two hidden versions to point to the same state id as the default (that is reconciling basically) Change the state 0 in those two version to the state_id of the default as follows

7- Save and close

8- Go run the compress command

sdeversion -o compress [-N]  -u sde -p sde_password -i arcsde_service -s server_name
-D database

You should now see your state tree set to zero.