Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

From what I can find the version store will only clean up versions that are older than the oldest active transaction. Question: Is the oldest transaction database specific or will SQL Server keep all versions, regardless of the database, if there is an older transaction still active, period?

Backstory - SQL Server 2005 SP4 Enterprise hosting around 40 databases. TempDB is currently 102 GB, version store is around 98 GB. One of the applications hosted on the database instance has an open transaction that is 40 days old based on sys.dm...database_transactions. Two separate large databases had extremely heavy use over the last month and we saw consistent TempDB growth coinciding with these operations. We expected some growth. We did not expect it to keep growing. Question: Are the versions stored in TempDB's version store from these two separate databases still there because a third independent database has a connection that is 40 days old and shows an open transaction_state?

Perfmon counters: Version store is continually growing in the few hours I have tracked it this morning. Version Generation Rate AVG is around 30 kb/s, Version Cleanup rate is 0 kb/s.

Plenty of space left for TempDB, there are around 300 GB of total data files for all user databases, TempDB has grown on average 350 MB per day for each of its 8 data files since the last restart. This behavior is abnormal and investigation revealed the large version store

Answers to comment questions so as not to have a long running comment section:

Q: Why auto-growth on tempdb? A: TempDB is set to initialize at a size we have found to be appropriate for most of the time. We allow auto-growth in order to handle abnormal database activity. We monitor auto-growth as well.

Q: How do you know the transaction is active and not just an active connection? A: transaction_state says active in sys.dm_tran_active_snapshot_database_transactions and other stuff. Activity Monitor says each connection has 1 open transaction.

Q: Why is your app so stupid? A: Its third party. One of many on this instance. I do not know if the behavior is abnormal, or easily fixed.

RESOLUTION

The open transaction(s) where preventing any version store cleanup, so Jon was right, version store cleanup is done independent of databases. Closing the offending transactions allowed version store cleanup to commence. Current theory behind why is from Jon Seigel

The version store can only clear versions based on the oldest active transaction within the entire instance, to support the use of transaction-level snapshot isolation across multiple databases simultaneously.

If anyone knows for certain or can prove this please do

Referenced question: find-transactions-that-are-filling-up-the-version-store

Referenced documents:
TempDB 2005 WP
Teratrax tuning tempDB
Idera Demystify Tempdb

share|improve this question
2  
Just because a connection has been open for a long time doesn't mean there's an active transaction. Which is it? Are you saying there's a transaction that's been active for 40 days? –  Jon Seigel Jul 8 '13 at 17:18
1  
Why are you letting tempdb continuously autogrow? I hope it's not at the defaults, but if you know tempdb is growing at this rate, until you solve the version store issue, wouldn't it make sense to proactively grow it once instead of death by 1000 autogrows? –  Aaron Bertrand Jul 8 '13 at 17:18
    
@Cougar9000 but why ANY growth events per day? –  Aaron Bertrand Jul 8 '13 at 18:26
    
From that query, what is the begin_time value for the active transaction? –  Jon Seigel Jul 8 '13 at 21:54
1  
"One of the applications hosted on the database instance has an open transaction that is 40 days old.." This cannot be doing your backups any favors either. Seriously, the best fix here is to stop doing that. Any program or product that holds a transaction open for such extended periods is very broken. –  RBarryYoung Jul 9 '13 at 18:08

2 Answers 2

up vote 5 down vote accepted

(Note: this isn't a 100% certain answer, and I don't have references, so let me know if you can prove otherwise.)

The version store can only clear versions based on the oldest active transaction within the entire instance, to support the use of transaction-level snapshot isolation across multiple databases simultaneously. That simply wouldn't work if "old" versions within one database were cleared out in the middle of another database's snapshot transaction.

So, if there's a very old open transaction, the version store will not be able to clear until that transaction either commits or rolls back.

share|improve this answer
    
Thanks Jon. I'm going to coordinate with the app users to close or kill these transactions and see what happens –  Cougar9000 Jul 9 '13 at 4:23
    
@Cougar9000: Okay. Again, I would be really wary of rolling them back because it could involve a lot of extra work on the SQL side to do the rollback. –  Jon Seigel Jul 9 '13 at 16:36
    
Yes, always wary. I go through a pretty extensive investigation before killing anything on the database. Your conversation and comments helped me isolate that user and that application as the primary suspect in preventing version store cleanup –  Cougar9000 Jul 9 '13 at 17:47

Is the oldest transaction database specific or will SQL Server keep all versions, regardless of the database, if there is an older transaction still active, period?

SQL Server ensures that it keeps all row versions that might be needed.

  • The version store is shared between all databases on the instance.
  • The version store is made up of a number of "append only" storage units (ASUs).
  • A new ASU is created every minute, if needed.
  • A new ASU is not created if no row versions are generated.
  • The current ASU is associated with a transaction when it starts.
  • A transaction continues to write row versions to the same ASU until it completes.
  • An ASU will generally contain row versions from many sessions, databases, tables, and indexes.
  • Individual row versions are not removed - only complete ASUs are.
  • An ASU is only removed when SQL Server can guarantee it is no longer needed.
  • ASU cleanup is performed by a background thread that wakes up every minute.

To expand on that last point, an ASU can only be removed when:

  1. All transactions targeting that ASU have completed.
  2. All transactions that might need versions from that ASU have completed.
  3. All earlier ASUs have been removed.

For more details, see the following resources:

Row Versioning Resource Usage - MSDN
Working with tempdb in SQL Server 2005 (Word Document)

A series of blog articles by Sunil Argawal from the SQL Server Storage Engine Team:

Version Store Basics
Version Store Logical Structure
Version Store Growth and Removing Stale Versions

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.