Looking to understand how much space the version store is using in your SQL Server tempdb? The sys.dm_tran_version_store_space_usage dynamic management view is your go-to. It provides a table showing the total tempdb space consumed by version store records for each database.
This view is super efficient and inexpensive to run because it doesn't dig into individual version store records. Instead, it gives you an aggregated view of the version store space used in tempdb on a per-database basis. That makes it perfect for quick checks and monitoring!
Retrieve Version Store Space Usage
SELECT DB_NAME(database_id) AS [Database Name],
reserved_page_count AS [Version Store Reserved Page Count],
reserved_space_kb / 1024 AS [Version Store Reserved Space (MB)]
FROM sys.dm_tran_version_store_space_usage WITH (NOLOCK)
ORDER BY reserved_space_kb / 1024 DESC
OPTION (RECOMPILE);
This query will give you a clear breakdown of the version store space by database, ordered from largest to smallest.
For more SQL Server insights, check out this article on getting your TempDB files count.
You can also find more detailed documentation on sys.dm_tran_version_store_space_usage on the Microsoft Docs website.
