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!
