TempDB Version Store cleanup

sql-server sql-server-2005 add tag

Cougar9000 (imported from SE)

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][1]

Referenced documents:
[TempDB 2005 WP][2]
[Teratrax tuning tempDB][3]
[Idera Demystify Tempdb][4]

[1]: https://dba.stackexchange.com/questions/36382/find-transactions-that-are-filling-up-the-version-store?rq=1
[2]: http://technet.microsoft.com/en-us/library/cc966545.aspx#EGAA
[3]: http://www.teratrax.com/tempdb-sql-server-performance-tuning/
[4]: http://www.idera.com/resourcecentral/whitepapers/demystify-tempdb-performance-and-management

Top Answer

Paul White (imported from SE)

>*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][1]  
[Working with tempdb in SQL Server 2005][2]

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

[Version Store Basics][3]  
[Version Store Logical Structure][4]  
[Version Store Growth and Removing Stale Versions][5]


  [1]: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms175492(v=sql.105)
  [2]: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/administrator/cc966545(v=technet.10)
  [3]: https://docs.microsoft.com/en-us/archive/blogs/sqlserverstorageengine/managing-tempdb-in-sql-server-tempdb-basics-version-store-simple-example
  [4]: https://docs.microsoft.com/en-us/archive/blogs/sqlserverstorageengine/managing-tempdb-in-sql-server-tempdb-basics-version-store-logical-structure
  [5]: https://docs.microsoft.com/en-us/archive/blogs/sqlserverstorageengine/managing-tempdb-in-sql-server-tempdb-basics-version-store-growth-and-removing-stale-row-versions

Answer #2

Jon Seigel (imported from SE)

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.

2 Answers