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