Rebuild Indexes – hangs

1294
5
01-21-2019 02:17 AM
DavidO_Donnell
New Contributor II

I am trying to rebuild Indexes including system indexes and it runs for about 10 minutes and then just stops.   

I have left it for several hours and the trace file from SQL Server just does not increment, it is as if the process just hangs.

The database is SQL Server 2016 on Amazon RDS.  I have run trace files for other operations like compress database and that works.

Here is the link to what I am following but adapted for our database connection details:

Any ideas on what to do next?

0 Kudos
5 Replies
George_Thompson
Esri Frequent Contributor

Are you running this on a local ArcGIS Pro client or a ArcGIS Pro client in the same AWS availability zone?

--- George T.
DavidO_Donnell
New Contributor II

Hi George,

Thank you for your response, here are some more details to explain the architecture:

1. Everything is hosted in one Amazon AWS Region (US East - N.Virginia) and also in the same Availability Zone in a VPC (Virtual Private Cloud)
2. The ArcGIS Pro client is running on a MS Windows Server 2012 R2 on a private subnet
3. The RDS MS SQL Server 2016 and is insidethe same Availability Zone.

0 Kudos
JonathanFarmer_oldaccount
Occasional Contributor III

David,

It sounds like you may be running this via a Python script. Is the behavior any different if you run the Rebuild Indexes tool directly from Pro? What about ArcMap?

I'm curious if there are a whole lot of indexes that need rebuilding and it is bogging down somewhere. But not sure how often this is run and how many edits have been made since the last time.

Jonathan

DavidO_Donnell
New Contributor II

Yes I am running it with Python scripts but have also ran it in ArcMap  & ArcGIS Pro using the Toolbox and I can see the same symptoms.

Yes there is a whole lot about 500+ that are highly fragmented and maintenance has not been regular. This was identified as a gap and hence now my involvement due to my database skills. My ArcGIS knowledge is limited at this moment but am scheduled to take some courses with ESRI.

Rebuilding the Indexes directly on the SQL Server database is something we are considering. This as I understand it should not have any adverse effect if we do this during an outage time. 

Do you think we should do this first and then see what happens?

0 Kudos
JonathanFarmer_oldaccount
Occasional Contributor III

David,

Yes, you can rebuild the indexes from the SQL Server side if you like. We have the tools in place on our end to do it and we'd prefer users use those tools when possible. But in this case, if we can get the indexes rebuilt on the database side and then run the Rebuild Indexes tool again from ArcGIS to confirm it works as expected, that would be OK.

Jonathan