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:
- All transactions targeting that ASU have completed.
- All transactions that might need versions from that ASU have completed.
- All earlier ASUs have been removed.
For more details, see the following resources:
A series of blog articles by Sunil Argawal from the SQL Server Storage Engine Team: