AnsweredAssumed Answered

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

Question asked by csgeosol1 Champion on Feb 15, 2018
Latest reply on Feb 28, 2018 by csgeosol1

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:


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?