A rows not liked to version are not compressing

[forestknutsen1]

We have a feature class in sde that is not compressing as expected.

• database Oracle
• ArcGIS version 10.6.1

My understanding is that if an A row edit state is not linked to a version it is eligible for compression. So, I have made up a SQL query to find said rows.

--versions that join to states
with lina as
 (select s.lineage_name
    from XXXX.a106 a
    join sde.states s
      on a.sde_state_id = s.state_id),
linv as
 (select v.owner, v.name, s.lineage_name
    from sde.versions v
    join sde.states s
      on v.state_id = s.state_id)
select linv.owner, linv.name, count(*) as a_row_edit_num
  from lina
  left join linv
    on linv.lineage_name = lina.lineage_name
 group by linv.owner, linv.name
 order by 3 desc

This query is reporting ~500,000  A rows without a version. But when I compress none are removed. Before I compress I am: 

• Disconnecting all users
• Prohibiting new connections
• Ensuring sde.table_locks has no rows

So, I am not sure what I am missing. Is there something wrong with my assumption? Or is my query flawed? 

Thanks!

[Robert_LeClair]

Hmmm...are you using eGDB replication and/or archiving?  This would pin states in the Delta tables certainly.  Another thing to try is to open your sde.VERSIONS and see which child VERSIONs are pointing to a State_ID of zero.  Try to reconcile/post that version with DEFAULT.  Keep working through this workflow until the DEFAULT version points to zero.  What is the result?

[forestknutsen1]

Thanks fro the feedback Robert.

1. No we are not using  eGDB replication and/or archiving. 
2. Reconciling and posting
   1. We have a large number of versions so reconciling and posting them all is impossible.
   2. "Another thing to try is to open your sde.VERSIONS and see which child VERSIONs are pointing to a State_ID of zero." -- how do I find the version that is pointing to state 0? All of are state ids are advanced well past state 0. And they all have default as a parent. 
3. Lastly, my core question is this: if a delta row can not be linked back to a version with the above sql should it be available for compression? In other words if rows that are not linked to a versions are not getting compressed there is an issues with the versioning in the geodatabase. 

[Robert_LeClair]

Sorry for the delayed response - life/work got super busy!

To your questions - in the sde.versions table, see what version is pointing to 0 by looking at the State_ID column.  Default is at 316113 and other versions are at other numbers.  I can't see the full table so hard to know what version is at 0.  Can you sort ascending perhaps?  

When you issue the compress, are all connections to the eGDB released or are there users/services still connected to the eGBD when the compress command starts?