topic Re: Optimizing geodatabase in Data Management Questions

Optimizing geodatabase

EmilyL — Thu, 22 Mar 2018 18:51:08 GMT

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?

Re: Optimizing geodatabase

JakeSkinner — Thu, 22 Mar 2018 19:01:20 GMT

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

Re: Optimizing geodatabase

DanPatterson_Retired — Thu, 22 Mar 2018 19:39:08 GMT

And there is a whole load of other tools

http://pro.arcgis.com/en/pro-app/tool-reference/data-management/an-overview-of-the-geodatabase-administration-toolset.htm

Re: Optimizing geodatabase

EmilyL — Thu, 22 Mar 2018 19:51:42 GMT

Thank you very much, Jake!

Re: Optimizing geodatabase

EmilyL — Thu, 22 Mar 2018 19:53:10 GMT

Thank you very much, Dan!

Re: Optimizing geodatabase

EmilyL — Fri, 23 Mar 2018 17:21:13 GMT

Hi Jake,

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

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!

Re: Optimizing geodatabase

JakeSkinner — Mon, 26 Mar 2018 20:10:09 GMT

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

Re: Optimizing geodatabase

BlakeTerhune — Mon, 26 Mar 2018 22:58:02 GMT

Here's my experience with this adventure.

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

Re: Optimizing geodatabase

EmilyL — Tue, 27 Mar 2018 16:31:45 GMT

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!

Re: Optimizing geodatabase

EmilyL — Tue, 27 Mar 2018 16:32:13 GMT

Thank you, Blake.