September 16, 2012 at 6:01 pm
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
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply