Implicit Transaction On - Snapshot Isolation - Ever Increasing Version Store

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Implicit Transaction On - Snapshot Isolation...

Implicit Transaction On - Snapshot Isolation - Ever Increasing Version Store

Rate Topic

Display Mode

Topic Options

Author

Message

b0b555

Posted Sunday, September 16, 2012 6:01 PM

Old Hand

Group: General Forum Members
Last Login: Sunday, June 16, 2013 9:32 PM
Points: 326, Visits: 485

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?

Cheers

Post #1359933

« Prev Topic | Next Topic »

Permissions