SDE Compress triples size of geodatabase

515
10
10-28-2019 08:06 AM
TimAnderson3
New Contributor II

My plan is to load roughly 400gb of vector data into an enterprise geodatabase. I just recently got started, and let "compress geodatabase" run over night. When I came back in, my SDE had grown from 4gb to about 16gb after the compress completed. I am using Desktop 10.6 and Oracle 12.2. If compressing increases size 2-3x this is going to give me problems. any suggestions on what may be broken?

0 Kudos
10 Replies
Asrujit_SenGupta
MVP Regular Contributor

How are you checking\calculating the size of the database?

0 Kudos
TimAnderson3
New Contributor II

I am basing it off of the size of the sde tablespace 

0 Kudos
George_Thompson
Esri Frequent Contributor

Agree with Asrujit and do you have lots of versioned editing happening?

--- George T.
0 Kudos
TimAnderson3
New Contributor II

there is no versioned editing right now. I am building what will eventually become the parent database

0 Kudos
George_Thompson
Esri Frequent Contributor

If there is no versioned editing, then there is not a need to compress the Oracle geodatabase. I have also not seen it grow from a compress when finished.

Compresses an enterprise geodatabase by removing states not referenced by a version and redundant rows.

Under the tool usage: This tool is not applicable for enterprise geodatabases that do not use versioning.

Compress—Help | ArcGIS Desktop 

The compress operation and geodatabases—ArcGIS Help | ArcGIS Desktop 

Do you have everything in a single tablespace (i.e. SDE, DATA, etc.)?

Was there any other data being loaded into the geodatabase during that same time?

What does the sde_compress table report back for that process?

--- George T.
0 Kudos
TimAnderson3
New Contributor II

Thank you for your replies. To be clear, I do understand that  I have no real need to compress at this point. We are moving to a new version of Oracle and I just took it as an opportunity to make sure things are still working as they should be, and it seems as if we have a glaring issue! To answer your questions, all data is going to the SDE tablespace, and there was no data being loaded during the compression. That was my first thought, but the problem is persistent even if I just import a very small dataset that only takes a few seconds to load. I am not in the office at the moment so I cannot look at the compress log for specifics, but it did reduce the states from "several" to 1, which was expected, and reported it as a success in the table. I did see that the undotbs.dbf is growing very large in addition to the sde tablespace, and I feel like figuring that part out may lead me to a resolution. 

0 Kudos
George_Thompson
Esri Frequent Contributor

I would contact technical support and work with an analyst to narrow down the issue of it growing larger (especially as much as you said) after a compress.

--- George T.
0 Kudos
TimAnderson3
New Contributor II

I have a ticket open with support, I figured I would post here as well hoping someone has had a similar experience

0 Kudos
George_Thompson
Esri Frequent Contributor

One thing that I just thought of is this: If the tablespace is on auto growth and during the compress or another operation, it needed that space. The TBS files will NOT compress back down to the smaller level when completed. The tablespace will stay the larger size and not grow till it is needed again.

Run the following SQL to see how much of the tablespace is actually being used:

clear breaks
 SET linesize 130
 SET pagesize 60
 break ON tablespace_name skip 1
 col tablespace_name format a15
 col file_name format a50
select * from dba_tablespace_usage_metrics order by used_percent desc;‍‍‍‍‍‍‍‍‍‍‍‍‍‍

That will help determine what may be going on.

--- George T.