How to compress edits without unregistering as versioned?

[cjms]

I have a versioned feature dataset in my Enterprise Geodatabase. My feature dataset is versioned traditionally with the move edits to base unchecked.

If I right click my feature dataset, I can go to Properties --> Manage --> Uncheck Versioning.

I then get the warning, "The following datasets contain edits which haven't been compressed"

I know I can click the Yes option at the bottom to compress edits before unregistering. However, is there a way for me to compress these edits without unregistering the feature dataset?

I'm not super familiar with versioning. Having read through some of the documentation, I've run the Reconcile and Compress tool, but still get that warning. If I right click my connection (logged in as the SDE user which has admin privileges) and go to the Version tab, I only see the default in there. I feel like I'm missing a step or have a misunderstanding of how traditional versioning works.

Edit: I disconnected other users and unchecked the accept connections at the time of these screenshots.  

[DavidSolari]

Record compression (how much is in the business table vs the A/D delta tables) is orthogonal from how many active versions you have. It's possible for a table with only 1 version to still have data in the delta tables because the SDE tables have determined some of those edits belong to an active "state" and shouldn't be relocated. Start by ensuring there are no active connections to the database aside from the one you're running the Compress tool from. If you still have data in the delta tables then contact your support rep and work with them to clear out the stuck states, this'll involve making direct edits to SDE tables and runs the risk of EGDB corruption but there is a process to do this safely.

[cjms]

What do you mean by active state? At the time of the screenshot and running the tools, I disconnected all users and unchecked the accept connections property.

[DavidSolari]

EGDBs group versioned edits into "states", where the state is chosen based on things like creating new versions, adding a replica etc. If you have something that's expecting to talk to a specific state then that state can't be removed, which means the related edits have to stay in the delta tables. I don't know exactly how things work but if you have no child versions, no replicas and no other connections to the database then a compress should clear out the delta tables.

Unless a state entry is "stuck" in the system tables, that's what support can walk you through.

[George_Thompson]

If you are using SQL Server, I have a SQL query that creates a temp table and list the # of rows in the delta tables. USE at your OWN RISK.......

 CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),      
       reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18), 
       unused VARCHAR(18))  
EXEC       sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' ' 
SELECT     TableName,CONVERT(bigint,rows) AS NumberOfRows, 
            CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB 
FROM       #RowCountsAndSizes where TableName like '[ad]%'   
ORDER BY   NumberOfRows DESC 
DROP TABLE #RowCountsAndSizes 

 This could allow you to help determine if there is some data stuck somewhere.

[cjms]

Thanks for the function George. I queried the delta tables, a_<id> and d_<id> , and can confirm there are rows in those tables. I would prefer not to run update statements directly in the DB tables though, at least the ESRI page recommends not doing that.

[JakeSkinner]

@cjms do you have ArcGIS Server services accessing your Enterprise Geodatabase.  If you do, you will want to make sure these services are stopped.  Here is a script that will stop ArcGIS Server services, disconnect users, stop connections to the enterprise geodatabase, reconcile/post versions, compress the database, allow connections again, rebuild stats/indexes, start ArcGIS Server services, and create a report of the before/after of your delta tables.