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.
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?