topic Re: compress sde database in Data Management Questions

compress sde database

TAMARADIEZ — Tue, 09 Apr 2013 11:58:59 GMT

Hi eveyone,

I am new administering arsde. Our system is ArcSDE 9.3.1 with SQL Server 2005. I started to work around 6 months ago and there is none in my team who knows about GIS. From the SDE_compress_log table I've found out the compress has not been ran for over 6 months. I was talking to our DBA and he mentioned about compressing the database in SQL. I am really confused about compressing the database. When our DBA mentioned he has compressed the database in SQL, is it the same as compressing the database through ArcCatalog?

Thanks,

Re: compress sde database

VinceAngelo — Tue, 09 Apr 2013 12:05:46 GMT

No, they're not the same. However, ArcSDE compression is only necessary if
versioned editing is taking place. How may versioned tables do you have?
How many rows in the A and D tables?

- V

Re: compress sde database

TAMARADIEZ — Tue, 09 Apr 2013 14:06:27 GMT

No, they're not the same. However, ArcSDE compression is only necessary if
versioned editing is taking place. How may versioned tables do you have?
How many rows in the A and D tables?

- V

Hi, the are 64 features classes registered as versioned with moving edits to the base. I don't know how to find out how many rows are in the A and D tables. I searched on google and found "You'll want to look at select registration_ID, owner,table_name from sde.table_registry order by table_name or select registration_ID, owner,table_name from sde_table_registry order by table_name to get the ID for your table"

Can I use that to find the number of rows on the A and D tables?

Many Thanks,

T

Re: compress sde database

VinceAngelo — Tue, 09 Apr 2013 14:19:11 GMT

In theory, with move-edits-to-base, there shouldn't be any rows queued in
the A or D tables. You'd need to whip up a script to query all the A and D
table pair row counts, or use your database browser to open all the A & D
tables. The list of {owner, registration_id} pairs generated from those
queries would be the basis of the query script.

- V

Re: compress sde database

TAMARADIEZ — Tue, 09 Apr 2013 14:52:29 GMT

Hi,

Thanks again.
When you say "In theory, with move-edits-to-base, there shouldn't be any rows queued in
the A or D tables", Does this means I do not have to compress the database? Do you still want me to try to find out the number of rows in the A and D tables?

Thanks,

T

Re: compress sde database

VinceAngelo — Tue, 09 Apr 2013 15:12:56 GMT

Nothing is perfect; it wouldn't hurt to confirm that the An/Dn tables are empty. If they are,
there's no point to compression.

- V

Re: compress sde database

MattSimmons — Tue, 09 Apr 2013 18:35:04 GMT

Maybe a more fundamental understanding of what compressing the database does, when done in ArcCatalog, would help your situation. In your editing workflow you either use, or do not use, versioned editing. Here's a link to understanding versioning at 9.3.
http://webhelp.esri.com/arcgisdesktop/9.3/index.cfm?TopicName=understanding%20versioning

Assuming you now understand versioning, when you register a feature class as versioned, the adds and deletes tables are created in the database. Adds and deletes tables don't exist if your feature class is not registered as versioned. When edits are made using versioning, the deltas are stored in these adds and deletes tables. Further on in the versioned editing process you will reconcile and post. Reconciling and posting means ArcGIS is "comparing" the edits of the database being edited to its parent database to see if any conflicts exist. Once reconciled, the edits are "posted" (or, committed, in a sense, but not actually committed...read on) to the default database. ArcGIS clients will now see the changes. However, the deltas are still in the adds and deletes tables, but the ArcGIS clients are referencing those tables, so data changes are visualized.

If you are using a third-party software (i.e. AutoCAD) to consume your gis data, that software probably won't be referencing the adds and deletes tables and thus the data changes won't be reflected. That's one time that compressing the database comes in. Compressing the database does a number of things. The first of which is that it "trims" the lineage of changes in the data. It trims the state tree which, among other things, saves on space. Another thing that compressing does (albeit a full compress) is that it moves the deltas from the adds and deletes tables to the base tables of the database, ensuring that those third-party programs are seeing all of the data changes.

You can compress through ArcCatalog as little or as much as you want, and ArcGIS will do as good of a job as it can in compressing. A full compress will not occur until all versions are reconciled, posted, and deleted; and there are no database replicas referencing the adds and deletes tables. A full compress is achieved when the state_id of SDE.DEFAULT equals 0. A successful compress does not always equal a full compress. A successful compress just means that ArcGIS did as good a job as it could trimming the state tree, considering versions may still exist and replicas have not been synchronized. Read up on database replication here:
http://webhelp.esri.com/arcgisdesktop/9.3/index.cfm?TopicName=Scenarios_using_distributed_data

As Vince stated, a full compress is not always necessary. To read up on compressing a database at version 9.3, read this:
http://webhelp.esri.com/ArcGISdesktop/9.3/index.cfm?TopicName=Compressing_an_ArcSDE_geodatabase_licensed_under_ArcGIS_Server_Enterprise

Hope this helps~

Re: compress sde database

TAMARADIEZ — Wed, 10 Apr 2013 10:02:09 GMT

Maybe a more fundamental understanding of what compressing the database does, when done in ArcCatalog, would help your situation. In your editing workflow you either use, or do not use, versioned editing. Here's a link to understanding versioning at 9.3.
http://webhelp.esri.com/arcgisdesktop/9.3/index.cfm?TopicName=understanding%20versioning

Assuming you now understand versioning, when you register a feature class as versioned, the adds and deletes tables are created in the database. Adds and deletes tables don't exist if your feature class is not registered as versioned. When edits are made using versioning, the deltas are stored in these adds and deletes tables. Further on in the versioned editing process you will reconcile and post. Reconciling and posting means ArcGIS is "comparing" the edits of the database being edited to its parent database to see if any conflicts exist. Once reconciled, the edits are "posted" (or, committed, in a sense, but not actually committed...read on) to the default database. ArcGIS clients will now see the changes. However, the deltas are still in the adds and deletes tables, but the ArcGIS clients are referencing those tables, so data changes are visualized.

If you are using a third-party software (i.e. AutoCAD) to consume your gis data, that software probably won't be referencing the adds and deletes tables and thus the data changes won't be reflected. That's one time that compressing the database comes in. Compressing the database does a number of things. The first of which is that it "trims" the lineage of changes in the data. It trims the state tree which, among other things, saves on space. Another thing that compressing does (albeit a full compress) is that it moves the deltas from the adds and deletes tables to the base tables of the database, ensuring that those third-party programs are seeing all of the data changes.

You can compress through ArcCatalog as little or as much as you want, and ArcGIS will do as good of a job as it can in compressing. A full compress will not occur until all versions are reconciled, posted, and deleted; and there are no database replicas referencing the adds and deletes tables. A full compress is achieved when the state_id of SDE.DEFAULT equals 0. A successful compress does not always equal a full compress. A successful compress just means that ArcGIS did as good a job as it could trimming the state tree, considering versions may still exist and replicas have not been synchronized. Read up on database replication here:
http://webhelp.esri.com/arcgisdesktop/9.3/index.cfm?TopicName=Scenarios_using_distributed_data

As Vince stated, a full compress is not always necessary. To read up on compressing a database at version 9.3, read this:
http://webhelp.esri.com/ArcGISdesktop/9.3/index.cfm?TopicName=Compressing_an_ArcSDE_geodatabase_licensed_under_ArcGIS_Server_Enterprise

Hope this helps~

Hi, thanks to you both for your help. Just to clarify although we are registering the features as versioned and moving edits to the base, we are not creating versions. Does this mean, there is not reconcile and postpone process during the compress?

Also, I can't find the A and D tables within the SQL tables. Could you please tell me the completed name of the tables. I have attached an screenshot of some tables i can see.

Regards,,

T

Re: compress sde database

VinceAngelo — Wed, 10 Apr 2013 10:29:58 GMT

I'd suggest you do further research into on the versioning model used by ArcGIS.
Versions are just names applied to states. The "move edits to base" model
utilizes the SDE.DEFAULT version, and modifies the state tree, then immediately
does the equivalent of "reconcile & post" and "compress" (though it only does
the compress if there is no conflict -- THIS is the reason you want to check
for outstanding edits, though you could also inspect the STATES and/or
STATES_LINEAGES tables).

The A and D tables would be owned by the spatial data owners; in fact, they
should NOT be owned by the SDE user.

- V

Re: compress sde database

PaulKroseman — Sun, 12 Dec 2021 04:37:50 GMT

The A and D tables are numbered based on the SDE_table_registry.registration_id field value (in ArcGIS 10.0).
If a feature class shows registration_id of 55, the add table is a55 and delete table is D55.

Each A/D table should be indexed. You can use the following SQL script which counts the number of indexed rows in each table and check the a### and D### tables for rows. Easier than trying to look at each A/D table for each feature class individually.

SELECT sysobjects.Name, sysindexes.Rows
FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE type = 'U' AND sysindexes.IndId < 2
ORDER BY sysobjects.Name

Re: compress sde database

TAMARADIEZ — Fri, 12 Apr 2013 08:21:18 GMT

Hi Thanks. Now I understand much better. I ran the script and sum all the records for the A and D tables.
A = 9969
D = 5299.

Is the above really bad? Or is it okay? I have heard the database could break if the compress is not ran. Could it happen

The feature class which has the largest number of A and Delete records is the ID =1511 with 6562 Adds and 2498 Deletes.

Regards,

T

Re: compress sde database

VinceAngelo — Fri, 12 Apr 2013 10:30:48 GMT

I suppose it depends on your definition of "break" -- the software is operating
according to design, and there's no reason to think it will stop working, but you
have edits that haven't made their way into the base tables.

- V

Re: compress sde database

TAMARADIEZ — Fri, 12 Apr 2013 12:20:04 GMT

Hi Thanks again.
I was told by an ESRI person if | did not compress the database it could break so I was concerned about the database will stop working. Some users are complaining about editing their feature classes being very slow. It is because I haven't move their edits to the base. Correct?

Thanks again.

T

Re: compress sde database

VinceAngelo — Fri, 12 Apr 2013 13:04:57 GMT

Let me put it this way: Failure to regularly compress a versioned geodatabase is not likely
to result in improved performance. Causality is difficult to predict, but best practice isn't.

- V

Re: compress sde database

TAMARADIEZ — Mon, 15 Apr 2013 13:53:58 GMT

Hi, Thanks for all your help.

Regards,

T