topic Re: ArcGIS Server map services - Idle/open transactions in SQL causing logging issues in ArcGIS Enterprise Questions

ArcGIS Server map services - Idle/open transactions in SQL causing logging issues

ChrisSmith7 — Thu, 15 Feb 2018 15:03:36 GMT

We're using ArcGIS Server 10.3, with map services sourced from MSSQL Server 2008 and 2012 instances. We have several map services created from non-spatial x/y data using x/y event layers in ArcGIS. They work fairly well, and we've had them in place for over a year.

Recently, we've encountered a scenario where our transactions logs on multiple instances were growing beyond disk capacity; however, we were unable to free-up space due to open transactions initiated by ArcSDE. This is a problem... but, after killing the sleeping spids related to the map services, we were able to shrink the transaction logs.

We are questioning why the map services are leaving open/idle transactions, though, it sounds like a developers decision on the part of Esri. The question has been raised before on Stack Exchange:

https://gis.stackexchange.com/questions/82235/why-is-arcmap-leaving-connections-idle-in-transaction

This concerns us a bit:

It's probably an artifact of their data provider object model, and a developer decision to start a transaction as part of setting up the connection (perhaps because they use binary cursors, which require a transaction for even reading? that's something the detailed statement log could tell you). They could probably re-work it to not do that, but you'll have to do some complaining.
....

A look at the SQL in the PostgreSQL logs shows that they are, as you suggest, using binary cursors. But they're declared WITH HOLD, so a transaction wrapper isn't strictly necessary. Regardless you're right: we'd have to do a lot of complaining for something that I can't prove (yet) is the cause of any problems.

Has anyone else encountered issues with ArcSDE keeping transactions open/idle, and if so, how did you mitigate? Maybe Esri can chime-in... is this something you're looking to address?

Thanks!

Re: ArcGIS Server map services - Idle/open transactions in SQL causing logging issues

George_Thompson — Thu, 15 Feb 2018 15:11:39 GMT

Adding a few more groups: https://community.esri.com/groups/geodatabase?sr=search&searchId=803f1232-2d78-40fe-add9-0918c458b9b4&searchIndex=0‌ https://community.esri.com/groups/enterprise-architect?sr=search&searchId=9636dfdc-f393-4641-ad49-4258e668727e&searchIndex=3‌

Re: ArcGIS Server map services - Idle/open transactions in SQL causing logging issues

ChrisSmith7 — Thu, 15 Feb 2018 20:24:31 GMT

Something else to add... we're noticing ArcGIS Server Manager isn't recycling the services after 24 hours (which is the setting in Manager). For instance, there are open spids from 2/13 for one of our services set to recycle every 24 hours.

The spids dropped-off after I stopped/started in Admin, so I should be able to script this, but I'm curious why the setting in Manager wouldn't be honored. Additionally, we're noticing some orphaned spids... SQL is pretty good at cleaning itself, so it sounds like there's an application level issue with ArcGIS related to a leak or maybe something missed in error handling.

Re: ArcGIS Server map services - Idle/open transactions in SQL causing logging issues

ChrisSmith7 — Wed, 28 Feb 2018 21:15:53 GMT

I opened a ticket with Esri... there are some suggestions that perms adjustments may help. Furthermore, there have been cases of AV interfering with a Server kill process regarding services recycling. As it happens, I am actually in process of upgrading from 10.3 to 10.6, and Esri believes this may actually resolve the issue.

I'll keep this thread posted with my findings.

Re: ArcGIS Server map services - Idle/open transactions in SQL causing logging issues

RiccardoKlinger — Wed, 15 Nov 2023 12:31:49 GMT

We are still facing a similar issue in ArcGIS Server 10.9.1 with SDE towards a postgreSQL database. we deal with the problem by enhancing the number of allowed open connections but this could not be the valid way.