Long running ArcCatalog issue
On rare occasions ArcCatalog leaves a transaction running with no SQL activity occurring. I am looking for anyone else who may have noticed this issue and, if I am really lucky, has a solution.
It appears to be associated (not yet 100% confirmed) with moving between database connections in the ArcCatalog tree view.
Environment is ArcGIS 10.5.1 running on SQL Server backend
As discussed in http://desktop.arcgis.com/en/arcmap/latest/manage-data/geodatabases/concurrency-and-locking.htm ‘Beginning with ArcGIS 10.4, geodatabases in SQL Server must have the SQL Server database options READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION set to ON, and ArcGIS uses the READ COMMITTED isolation level for transactions’
From a SQL perspective this causes the history of a transaction to be kept in TEMPDB until the transaction ends.
If a transaction remains open, the space in TEMPDB is not removed. In addition, any TEMPDB transaction information for any other transactions across the whole SQL Instance will be retained until the original long running transaction completes (This is not 100% true, but sufficient detail to explain the problem).
Accordingly, it becomes important that ArcGIS transactions do not run for extended (like hours) periods of time.
We are seeing the situation that, on rare occasions a user of ArcCatalog has an active transaction running for hours, when, from his perspective, all he is doing is looking at the treview of data base connections. When we go to the client PC we see an ArcCatalog screen like…
If we then right-click on ‘SDIDIV_SQLP2_PRD2.sde’ and then release the ‘right-click’ the problem is resolved.
In a SQL trace (started just before the right-click) we see a series of SQL queries, followed by a ‘COMMIT’ of a transaction with a start time of 01/08/2018 10:51 and an endtime of 01/08/2018 14:37 (nearly 4 hours!)
My conclusion is that ArcCatalog has ‘forgotton’ to commit some processing at 10:51 (probably the user moving between database connections). My DeLorean is in for a service at the moment and so I cannot show you the trace around 10:51.
The simplest conclusion (from my perspective) is to ask ESRI to identify their coding error and fix it, but I think, without further documentation, that would be a little unrealistic.
A second option is to get the user to recreate the problem and perform a trace while he is doing this (This has so far been unsuccessful).
Given that we cannot identify root cause, the next option is to mitigate the situation.
• Can we set a transaction timeout?
• Can we set an idle timeout for a transaction?
Problem seems to be isolated to one user. His usage profile is such that he often switches between database connections, whereas a ‘normal’ user chooses a database connection and sticks with it for extended periods of time
Any thoughts welcome
