tempdb files and how full they are, the version store, and which sessions are using tempdb right now. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and 2025.

-- 11_TempDB.sql - tempdb files, how full they are, and which sessions are using it now. Read-only.
SET NOCOUNT ON;
USE [tempdb];
SELECT name, type_desc, physical_name, size / 128 AS size_mb, FILEPROPERTY(name, 'SpaceUsed') / 128 AS used_mb,
       CASE WHEN is_percent_growth = 1 THEN CONVERT(varchar(10), growth) + '%' ELSE CONVERT(varchar(20), growth / 128) + ' MB' END AS growth
FROM sys.database_files;

SELECT SUM(user_object_reserved_page_count) / 128 AS user_objects_mb, SUM(internal_object_reserved_page_count) / 128 AS internal_objects_mb,
       SUM(version_store_reserved_page_count) / 128 AS version_store_mb, SUM(unallocated_extent_page_count) / 128 AS free_mb
FROM sys.dm_db_file_space_usage;

SELECT TOP (10) su.session_id, s.login_name, s.host_name, s.program_name,
       (su.user_objects_alloc_page_count + su.internal_objects_alloc_page_count) / 128 AS allocated_mb
FROM sys.dm_db_session_space_usage AS su JOIN sys.dm_exec_sessions AS s ON s.session_id = su.session_id
WHERE su.user_objects_alloc_page_count + su.internal_objects_alloc_page_count > 0
ORDER BY allocated_mb DESC;