SQL Script – Tempdb Space Usage Monitoring

Updated on October 17, 2022     Kisan Patel

Tempdb is a system database inside SQL Server, used primarily for internal and temporary operations. In the case of troubleshooting performance, DBAs focus mostly on the size of Tempdb, what is being stored here and why?

Tempdb is comprised of two files, a data file (could be more than one) and a log file. Upon executing workloads, there is a significant increase in the size of these two files, while the Available Space will continue to decrease, causing SQL Server to not function at its optimum.

The following DMV, sys.dm_db_file_space_usage, and a system catalog – sys.database_files, provide the required output for purposes of troubleshooting Tempdb.

USE [tempdb];
GO

select getdate() AS runtime, SUM(user_object_reserved_page_count) * 8 as usr_obj_kb,
SUM (internal_object_reserved_page_count) * 8 as internal_obj_kb,
SUM (version_store_reserved_page_count) * 8 as version_store_kb,
SUM (unallocated_extent_page_count) * 8 as freespace_kb,
SUM (mixed_extent_page_count) * 8 as mixedextent_kb
FROM sys.dm_db_file_space_usage

USE [tempdb];
GO

select name AS [Logical Name], size/128.0 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/129.0 AS [Available Space In MB]
FROM sys.database_files

select * from sys.dm_tran_version_store

Source: https://www.sqlservergeeks.com/tempdb-space-usage-monitoring/