Get tempdb version store space usage by database

Returns a table that displays total space in tempdb used by version store records for each database. sys.dm_tran_version_store_space_usage is efficient and not expensive to run, as it does not navigate through individual version store records, and returns aggregated version store space consumed in tempdb per database.

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);

Get TempDB files count Himanshu Patel SQL Server Consultant developer & Administrator –

dm_tran_version_store_space_usage