Implicit Transaction On - Snapshot Isolation - Ever Increasing Version Store

• b0b555

  Mr or Mrs. 500

  Points: 561

  More actions

  September 16, 2012 at 6:01 pm

  #262011

  We have a 3rd party application:

  - where the target database has "snapshot isolation on"

  - where some connections from the application have "set implicit_transactions on"

  - do not seem to execute a "commit tran" following select statements, but do on update/insert/delete.

  - have connections left open (Status = Sleeping, Command = Awaiting Command) for long periods (current oldest last activity was 12 days ago)

  - these open connections typically have a shared database lock on the application database and an exclusive page lock or 2 on TempDB

  We have an ever increasing version store, currently approx 43 GB, which only gets fixed by the more or less monthly reboots for patching.

  From what I can tell, until these "old" transactions end (either get committed or killed/rolled back), the version store clean up process will not remove any version store allocations. Stats from dm_os_performance_counters for the version store are:

  Transactions81

  Snapshot Transactions0

  Update Snapshot Transactions0

  NonSnapshot Version Transactions59

  Longest Transaction Running Time1863111

  Update conflict ratio0

  Update conflict ratio base0

  Free Space in tempdb (KB)1306944

  Version Generation rate (KB/s)44764728

  Version Cleanup rate (KB/s)8800

  Version Store Size (KB)44755928

  Version Store unit count28764

  Version Store unit creation28900

  Version Store unit truncation136

  I am thinking that as these "old" transactions are not holding any locks on the application database apart from the database share lock and the TempDB locks, then there are no risks in killing these connections, which should enable the version store cleanup process to work.

  Or am I missing something?

  Cheers