compress sde database

[TAMARADIEZ]

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,

[VinceAngelo]

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

[TAMARADIEZ]

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

[VinceAngelo]

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

[TAMARADIEZ]

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

[VinceAngelo]

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

[MattSimmons]

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

Hope this helps~

[TAMARADIEZ]

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

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

[VinceAngelo]

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

[PaulKroseman]

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