Optimizing geodatabase

1913
9
Jump to solution
03-22-2018 11:51 AM
EmilyL
by
Occasional Contributor

What is the correct sequence of optimizing an enterprise geodatabase?

Analyze > Rebuild Indexes > Compress?

Should I stop the GIS services before rebuilding indexes and compressing the database?

0 Kudos
1 Solution

Accepted Solutions
JakeSkinner
Esri Esteemed Contributor

Hi Emily,

If services are referencing feature classes in your enterprise geodatabase, these should be stopped when you run a compress so that the necessary locks are released.  After a compress, it's recommended to rebuild indexes and stats.  Take a look at the following tool, it may help you automate some of this procedure:

https://community.esri.com/docs/DOC-9914-compress-geodatabase-tool 

View solution in original post

9 Replies
JakeSkinner
Esri Esteemed Contributor

Hi Emily,

If services are referencing feature classes in your enterprise geodatabase, these should be stopped when you run a compress so that the necessary locks are released.  After a compress, it's recommended to rebuild indexes and stats.  Take a look at the following tool, it may help you automate some of this procedure:

https://community.esri.com/docs/DOC-9914-compress-geodatabase-tool 

EmilyL
by
Occasional Contributor

Thank you very much, Jake!

0 Kudos
EmilyL
by
Occasional Contributor

Hi Jake,

 

Thank you for the automation file again. But when I tried to run it, I got the following error message:

compress error

I am an administer of the geodatabase, but not the owner/creator.

I appreciate the automation file, but let me try to see/understand it step by step first. I still have some more questions please:

1.  I’ve edited one layer in the geodatabase, but I still do the compression at the geodatabase level, not at the layer level, correct? 

2.  Since the compression is at the geodatabase level, I’d need to stop all services that came from this geodatabase, not only that particular one layer I’ve edited, correct?  What happen if I forget to stop the services first before running compression?  

3.  Is compress by R-click on the geodatabase > Administration > Compress Database same as the GP Compress Tool?  

4. Both results "SUCCESS", but the specific layer still draws slowly.  I still should continue to perform other maintenance such as rebuild indexes and analyze, correct?

5. Should I just perform them all under R-click to the geodatabase > Administration > Geodatabase Maintenance?

6.  The only time I absolutely need to stop services and unlock all layers is to recalculate Spatial Indexes for the specific layer.  If not, the option would be grayed out. 

  

7. What is the difference between Rebuild and Recalculate indexes?

Sorry for all the questions and thank you very much!

0 Kudos
JakeSkinner
Esri Esteemed Contributor

Can you send a screen shot of how you have the tool set up before you execute it?

1. You actually can only compress an entire geodatabase.  You don't have the ability to compress a single feature class.

2.  If you don't stop the services, the feature class will be skipped and not compressed since there is a lock.

3.  Yes

4.  You will need to check the corresponding A&D tables of the feature class to make sure they are cleared out.  This is accomplished in by accessing SQL Server Management Studio, locating the table name in the dbo.sde_table_registry table.  The registration_id will represent the A and D tables.  For example, if this is 35 you can query the <owner>.a35 and <owner>.d35 tables to see if they contain any rows after the compress

5.  Yes, this will work as well.

6.  You will need to stop the service in order to rebuild the index

7.  I believe these may perform the same thing

EmilyL
by
Occasional Contributor

Thank you Jake for your reply. Very helpful!

Here is the screenshot of how I set up the Compress automation file.

1) If it runs successfully, it'd stop all services automatically, correct? 

2) Would compression still work (partially) if I only stop the service that associates to the specific layer that I edited?  Is it necessary to stop all services in order for compression to work?

3) The following tasks would be skipped on those layers if I haven't stopped those corresponding services, correct? When I was running them, I didn't get any success or error messages, so not sure if it's worked or partially worked at all.

Thank you again!

0 Kudos
DanPatterson_Retired
MVP Emeritus
0 Kudos
EmilyL
by
Occasional Contributor

Thank you very much, Dan!

0 Kudos
BlakeTerhune
MVP Regular Contributor

Here's my experience with this adventure.

https://community.esri.com/message/597806 

EmilyL
by
Occasional Contributor

Thank you, Blake.

0 Kudos