Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Implicit Transaction On - Snapshot Isolation - Ever Increasing Version Store Expand / Collapse
Posted Sunday, September 16, 2012 6:01 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:52 PM
Points: 331, Visits: 859
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:

Transactions 81
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?


Post #1359933
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse