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:
Snapshot Transactions 0
Update Snapshot Transactions 0
NonSnapshot Version Transactions 59
Longest Transaction Running Time 1863111
Update conflict ratio 0
Update conflict ratio base 0
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 count 28764
Version Store unit creation 28900
Version Store unit truncation 136
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?