Compress GeoDatabase to State 0

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. and You have one version SDE.DEFAULT and it is still pointing to state 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"


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- Now you might have different values, but look at the whole structure it should be the same

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

5- 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

6- Save and close

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

Or

Follow the steps below.

1. Reconcile and post all versions which are ready to be applied to the DEFAULT version. Alternatively, delete the versions.
   
2. Delete all versions, excluding the sde.DEFAULT version.
   
3. Verify no users are connected. A connection acquires a state lock and a locked state cannot be compressed.
4. Compress the database. -show me-
5. Verify the DEFAULT version references state_id 0 by describing the version with the ArcSDE utility sdeversion –o describe once the compress command completes. The output should show the value for the DEFAULT version’s state_id to be 0. The delta tables of the versioned class should be empty.
6. Analyze the database to update database statistics for best performance.