SQLITE files being created

Question

Have noticed in our Enterprise setup a large number of .sqlite files being created and causing downtime on our portal and apps. We have a script running to clear these down every night but some files being created during the day are over 8gb and a number of these cause issues before the script runs.

Is there a way to pinpoint the generation of these sqlite files and reduce their impact?

GeoffreyAplin · Answer

To close out my contribution here.  I raised a ticket with our Aussie distributer which became a formal BUG

BUG-000165475 has been addressed.

Description: Creating a query layer and publishing it as a map service to ArcGIS Portal will keep generating OIDMappingxxx.sqlite temp files upon usage, which will not be removed automatically and accumulate disk space.

ESRI Inc. advised Tuesday that the bug is fixed as follows "This issue is addressed in ArcGIS Enterprise 12.0 and ArcGIS Pro 3.6."

So until we move to ArcGIS Enterprise 12.0 or later, our temporary workaround of a batch script that runs at server startup to delete the files will continue.
We run the script at startup to ensure the files aren't locked or in use with any existing web service session.

Hope that helps to anyone reading.  Cheers from Australia

GeoffreyAplin · Answer

Hi from Australia.
We have similar challenges with these large files that only seem to be created for a single feature class best we can tell by looking at a sample of the files containing objectid and what appear to be the definition for a sql where clause.
1. We're on Arcgis enterprise 10.9.1 and was unaware of these files with earlier versions although it may relate to something added since version upgrade
2. Files are C:\Users\{arcgis service account}\AppData\Local\Temp on each arcgis server operating system drive.
3. I can't share a file as the 'where clause' values are sensitive data. Screenshot shows snip with objectid and primarykeyclause columns and values

Our local distributor support indicates theMakeQueryLayer Python function generates these but we can't identify use of this in our code so it remains a mystery.
We do use MakeFeatureLayer in ArcGIS Pro python code but not for this feature class and we're told that it doesn't apply for that function.

So any clues as to what scenario causes arcgis server to generate these would be appreciated as we're trying to identify cause and possible options to prevent their generation and probable performance impacts of this happening.  Are we missing an attribute index for a join operation or similar perhaps? How else could a web application trigger their generation.

Thanks for any insights.

archit_esri · Answer

Hello @CiarainSheridan ,

In order to have a better understanding of this, could you please share the following details:

* What is the version of ArcGIS Enterprise you are working with?
* What is the location where the files are being created?
* Is it possible for you share one of these sqllite files for running a check?

There are multiple things where these could be being utilized but would require some troubleshooting.

-Archit