topic Re: Is there an ESRI way to see if there is versioned data still in progress that needs compressing? in Geodatabase Questions

Is there an ESRI way to see if there is versioned data still in progress that needs compressing?

VaL — Fri, 02 Mar 2018 16:24:59 GMT

We have oracle 11 SDE database with some possible versioned feature classes.

Besides looking into the relevant tables involved in versioning is there any way to find out if a FC is versioned and might need compressing/finalizing.

Thanks for the input.

Re: Is there an ESRI way to see if there is versioned data still in progress that needs compressing?

JamesMacKay3 — Sun, 12 Dec 2021 09:27:25 GMT

You can get a list of versioned feature classes using something like this from the Python window:

arcpy.env.workspace = r"Database Connections\YourConnFile.sde"
for fc in arcpy.ListFeatureClasses():
    if arcpy.Describe(fc).isVersioned:
        print(fc)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍

You could do that for each distinct connection you've got, or put the SDE file paths in a list and iterate through them all together.

Edit: This will only work with standalone feature classes and would have to be extended to detect those inside of feature datasets...

Re: Is there an ESRI way to see if there is versioned data still in progress that needs compressing?

VaL — Thu, 08 Mar 2018 11:43:17 GMT

I will need to hit the database directly to see if there are versioned tables/FCs.

From these tables: STATES, STATE_LINEAGES, VERSIONS, and MVTABLES_MODIFIED which one is the best to query to get a definitive answer if there are versioned FCs/tables in the sde db?

I was counting how many records exist in the versions table, but there could be 1 record, but still there are versioned FCs. In this case state_id is > 0.

There are also DBs with more than on record in the versions table.

So what is the best way to query the SDE db to find out?

Re: Is there an ESRI way to see if there is versioned data still in progress that needs compressing?

Asrujit_SenGupta — Thu, 08 Mar 2018 11:51:04 GMT

Looking for this -- Example: Determining which datasets are versioned in a geodatabase—Help | ArcGIS Desktop ??

You can query the definition column of the GDB_Items table (or GDB_Items_vw view in Oracle) to return a list of feature classes that have the versioned value set to true (or 1, depending on the database).

Re: Is there an ESRI way to see if there is versioned data still in progress that needs compressing?

VaL — Thu, 08 Mar 2018 12:07:52 GMT

Thanks, strangely I can see one FC which I know is versioned but it is not in gdb_items_vw.

Also the sql in the example wont return any records but I know there are versioned FCs.

Re: Is there an ESRI way to see if there is versioned data still in progress that needs compressing?

VaL — Thu, 08 Mar 2018 12:39:16 GMT

I was wondering, would state_id <> 0 in the states table indicate presence of versioned data?

I dont care which FCs are versioned, but if there are any or not.

Re: Is there an ESRI way to see if there is versioned data still in progress that needs compressing?

Asrujit_SenGupta — Thu, 08 Mar 2018 17:45:36 GMT

If my understanding is correct, you just want to know whether the Geodatabase is fully compressed or it can be compressed further to reach State 0. If that is the case, simply check the Compress Log after performing a compress and check the End State.

You cannot perform the compress for a single Feature Class anyway, as it happens at the Geodatabase level.

You can see the results of each compression in ArcGIS for Desktop in the SDE_compress_log table. You can also check the SDE_versions table to see if the state ID for the DEFAULT version has returned to zero. If it has and there are no other outstanding versions, full compression has been achieved.

Geodatabase compression—Help | ArcGIS Desktop

Re: Is there an ESRI way to see if there is versioned data still in progress that needs compressing?

TinaMorgan1 — Mon, 12 Mar 2018 23:24:49 GMT

Right, my understanding of the question is that they are trying to find out which delta tables have records still in them, in other words, which feature class's delta tables still contain records that can be compressed to the base table.

I am not sure of a way to check the record count of delta tables in ArcCatalog, and the way I would do this would be to just query the database with SQL.

Depending on what your data looks like, you may get something like this by making a database view on the base table. Since Esri applications will be the base table + the delta tables, making a view on the base table alone could give you an idea of the differences.

Re: Is there an ESRI way to see if there is versioned data still in progress that needs compressing?

VaL — Tue, 13 Mar 2018 14:35:47 GMT

Let me double check my understanding of versioning here.

I have 2 major cases

Case 1: only one record in the sde.versions table with two subcases 1.1: state_id = 0 and 1.2 state_id > 0.

Case 2 more than one records in the sde.versions table with state_id > 0 for each of them

What I think this means is that one can have versioned data in the default version (case 1) with numerous states ( = additions/deletions). Also there can be other versions children to default (most simple case) with numerous states.

Is that correct?

What happens when the DB is compressed?

Re: Is there an ESRI way to see if there is versioned data still in progress that needs compressing?

TinaMorgan1 — Tue, 13 Mar 2018 16:00:26 GMT

So being picky on semantics here, versions do not hold data. So the default version doesn't 'hold versioned data'. It is simply a view that combines edits made within that version. The view uses the state_id which can be traced up the versions lineage (state_lineages) all the way up to the base table. Edits (record updates, inserts and deletes) are what hold states, and those edits must be made within a version so the version state_id only represents that.

So when you compress a geodatabase those edits (with state ids) are pushed from the delta tables into the base table. That's where the bulk of the action happens. The other tables (versions, lineages, etc) are for used to implement Esri's multi-user geodatabase functionality.

(first what kind of database are you using? SQL Server, oracle, ??) You can go into your database tables and you should see some tables like a## and d##. These are your delta tables. The number corresponds to the registration_id of the table_registry table. That's where your edits are in a versioned database. When you compress the geodatabase those a and d tables are truncated, and the edits are pushed into the base table.

This is an old article, but a good one...

Versioned tables in a geodatabase in Oracle

Re: Is there an ESRI way to see if there is versioned data still in progress that needs compressing?

VaL — Tue, 13 Mar 2018 16:22:02 GMT

Thanks. We use oracle.

I also got this idea, may be slightly wrong semantics.

But what would one record in the versions table with state_id > 0 mean?

Some feature classes are registered as versioned but not with a child version and there are edit in the A### and D### tables. Is that correct?

Re: Is there an ESRI way to see if there is versioned data still in progress that needs compressing?

TinaMorgan1 — Tue, 13 Mar 2018 16:36:00 GMT

if there is a record in the versions table that has a state_id of 0 then that means that version was created off of its parent when it too was at state zero, AND that no one has ever connected to that version and made edits within it. Basically you can delete it, as it has never been used.

Feature classes can be registered as versioned and that has no effect on how many versions you actually have. You may register every feature class in your geodatabase as versioned, but never create any transactional versions to edit within (other than DEFAULT which will always be there).

If there are edits in the a and d tables then that data has not been compressed. Period. If you ran a geodatabase compress and STILL see records in the a and d tables, then that means those edits are still being referenced by something, for example an open edit session or a version that has not been reconciled and posted.

Re: Is there an ESRI way to see if there is versioned data still in progress that needs compressing?

KenGalliher1 — Thu, 15 Mar 2018 02:47:14 GMT

This isn't an ArcGIS/Esri way but take a look at this tech article describing a SQL script that.. "reports information about a versioned geodatabase in Oracle. The output lists the number of versions, the number of versions blocking the DEFAULT version from being compressed to state 0 and the owner.names of the first 5 blocking versions, the number of states, the number of entries in the state_lineages table, and the number of rows in the versioned table and delta tables."

As has been mentioned in the other comments, to see what versioned feature classes have not had their edits posted and compressed to the base table is to look at the delta tables.

How To: Report geodatabase versioning statistics in an Oracle geodatabase

Re: Is there an ESRI way to see if there is versioned data still in progress that needs compressing?

KenGalliher1 — Wed, 05 May 2021 18:53:29 GMT

Also, try this query to check the count of unposted states on all versioned feature classes:

SELECT tr.table_name AS table_name , s.owner AS state_owner, count(*) AS total_unposted_states FROM sde.table_registry tr INNER JOIN sde.mvtables_modified mv ON tr.registration_id = mv.registration_id INNER JOIN sde.states s ON mv.state_id = s.state_id GROUP BY tr.table_name, s.owner;

Re: Is there an ESRI way to see if there is versioned data still in progress that needs compressing?

MiroUmlauf — Wed, 07 Apr 2021 00:10:25 GMT

Thank you Ken, that is brilliant. Had to just slightly adjust for our SQL server as:

SELECT tr.table_name AS table_name,
       s.owner AS state_owner,
       count(*) AS total_unposted_states
FROM sde.SDE_table_registry tr
    INNER JOIN sde.SDE_mvtables_modified mv
    ON tr.registration_id = mv.registration_id
    INNER JOIN sde.SDE_states s
    ON mv.state_id = s.state_id
GROUP BY tr.table_name, s.owner;