How Full TempDB Is Right Now, and Whether Its Files Are Set Up Correctly
TempDB Configuration and TempDB Hotspots already cover configuration baseline and allocation-contention diagnosis. These 2 scripts fill the gap between them: Get-TempdbUsage shows exactly how full each TempDB file is right now, broken down by user objects, internal objects, and version store. Get-TempDbFileBalance checks whether the files themselves are set up the way Microsoft recommends: equal size, equal growth, and 1 data file per logical CPU up to 8. Run it before contention becomes a problem.
Why TempDB Usage and File Balance Matter
- When TempDB fills, every session on the instance that needs temp space fails, not only one database. It is shared across the whole instance, with no per-database isolation.
- SQL Server spreads new allocations across the data files in proportion to the free space each one has. Unequal sizes skew that toward the largest file, which puts the contention back onto fewer files.
version_store_mbgrowing is a specific signal: a long-running transaction under snapshot isolation orREAD_COMMITTED_SNAPSHOTis holding row versions that cannot be cleaned up yet.- Matching the data file count to the CPU count does the most to reduce allocation page contention. It is usually set once at install and then forgotten.
When to Run These Scripts
- Get-TempdbUsage: TempDB is growing, an error says TempDB is full, or the TempDB volume is running out of disk. For the full incident walkthrough, see TempDB Is Full or Still Growing: Find What Is Using It, Right Now.
- Get-TempDbFileBalance: the first review of any inherited server, alongside the rest of the Server Inventory cluster.
- After adding or resizing TempDB files, to confirm the change left the files balanced.
- Alongside TempDB Hotspots when
PAGELATCHwaits point at allocation pages.
The Scripts
1. Get-TempdbUsage: Per-File Size, Free Space, and Allocation Breakdown
- Tested on: SQL Server 2025 (RTM CU8), Windows lab instance
- Last verified: 2026-10-12 (both scripts run; the screenshot and output below are from it)
- Permissions: VIEW SERVER STATE
- Safety: read-only, impact low
/*
Script Name : Get-TempdbUsage
Category : maintenance-and-reliability
Purpose : Show TempDB file sizes, free space, and allocation breakdown per file.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-temp-db-usage-and-file-balance/)
Requires : VIEW SERVER STATE
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT
df.file_id,
df.name AS logical_name,
df.type_desc AS file_type,
df.physical_name,
CAST(df.size * 8.0 / 1024 AS DECIMAL(10,2)) AS size_mb,
CASE df.max_size
WHEN -1 THEN NULL
ELSE CAST(df.max_size * 8.0 / 1024 AS DECIMAL(10,2))
END AS max_size_mb,
CASE df.is_percent_growth
WHEN 1 THEN CAST(df.growth AS VARCHAR(10)) + '%'
ELSE CAST(CAST(df.growth * 8.0 / 1024 AS INT) AS VARCHAR(20)) + ' MB'
END AS auto_growth,
CAST(fs.unallocated_extent_page_count * 8.0 / 1024 AS DECIMAL(10,2)) AS free_mb,
CAST((df.size - fs.unallocated_extent_page_count) * 8.0 / 1024 AS DECIMAL(10,2)) AS used_mb,
CAST(fs.user_object_reserved_page_count * 8.0 / 1024 AS DECIMAL(10,2)) AS user_objects_mb,
CAST(fs.internal_object_reserved_page_count * 8.0 / 1024 AS DECIMAL(10,2)) AS internal_objects_mb,
CAST(fs.version_store_reserved_page_count * 8.0 / 1024 AS DECIMAL(10,2)) AS version_store_mb,
CAST(100.0 * (df.size - fs.unallocated_extent_page_count) / NULLIF(df.size, 0) AS DECIMAL(5,2)) AS pct_used
FROM tempdb.sys.database_files AS df
LEFT JOIN tempdb.sys.dm_db_file_space_usage AS fs ON df.file_id = fs.file_id
ORDER BY df.type, df.file_id;
2. Get-TempDbFileBalance: File Count, Size, and Growth Configuration Check
/*
Script Name : Get-TempDbFileBalance
Category : monitoring
Purpose : TempDB data file configuration — checks for size imbalance, growth mismatches, percent-based growth, and file count vs CPU count.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-temp-db-usage-and-file-balance/)
Requires : VIEW SERVER STATE
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
WITH files AS (
SELECT
file_id,
type_desc,
name AS file_name,
physical_name,
CAST(size * 8.0 / 1024 AS DECIMAL(10,1)) AS size_mb,
CASE WHEN max_size = -1 THEN -1
ELSE CAST(max_size * 8.0 / 1024 AS DECIMAL(10,1)) END AS max_size_mb,
growth,
is_percent_growth
FROM sys.master_files
WHERE database_id = 2
),
data_stats AS (
SELECT
COUNT(*) AS data_file_count,
MIN(CASE WHEN type_desc = 'ROWS' THEN size_mb END) AS min_data_size_mb,
MAX(CASE WHEN type_desc = 'ROWS' THEN size_mb END) AS max_data_size_mb,
MIN(CASE WHEN type_desc = 'ROWS' THEN growth END) AS min_growth,
MAX(CASE WHEN type_desc = 'ROWS' THEN growth END) AS max_growth,
MAX(CASE WHEN type_desc = 'ROWS' AND is_percent_growth = 1
THEN 1 ELSE 0 END) AS any_pct_growth
FROM files
WHERE type_desc = 'ROWS'
),
cpu AS (
SELECT
cpu_count AS logical_cpus,
CASE WHEN cpu_count >= 8 THEN 8 ELSE cpu_count END AS recommended_files
FROM sys.dm_os_sys_info
)
SELECT
f.type_desc AS file_type,
f.file_id,
f.file_name,
f.physical_name,
f.size_mb,
CASE WHEN f.max_size_mb = -1 THEN 'Unlimited'
ELSE CAST(f.max_size_mb AS VARCHAR(20)) + ' MB' END AS max_size,
CASE WHEN f.is_percent_growth = 1
THEN CAST(f.growth AS VARCHAR(10)) + '%'
ELSE CAST(CAST(f.growth * 8.0 / 1024 AS DECIMAL(10,0)) AS VARCHAR(10)) + ' MB' END AS autogrowth,
f.is_percent_growth,
c.logical_cpus,
c.recommended_files,
s.data_file_count,
CASE WHEN f.type_desc = 'ROWS' AND s.data_file_count < c.recommended_files THEN 'TOO_FEW'
WHEN f.type_desc = 'ROWS' AND s.data_file_count > c.recommended_files THEN 'EXCESS'
ELSE 'OK' END AS file_count_check,
CASE WHEN f.type_desc = 'ROWS' AND s.min_data_size_mb <> s.max_data_size_mb THEN 'IMBALANCED'
ELSE 'OK' END AS size_balance,
CASE WHEN f.type_desc = 'ROWS' AND s.min_growth <> s.max_growth THEN 'IMBALANCED'
WHEN f.type_desc = 'ROWS' AND s.any_pct_growth = 1 THEN 'PCT_GROWTH'
ELSE 'OK' END AS growth_balance
FROM files f
CROSS JOIN data_stats s
CROSS JOIN cpu c
ORDER BY f.type_desc DESC, f.file_id;
How To Run From The Repo
Clone DBA Tools, initialize and run whichever script answers your question:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Per-file TempDB usage and allocation breakdown:
.\run.ps1 Get-TempdbUsage
# File count, size, and growth balance check:
.\run.ps1 Get-TempDbFileBalance
# Either against a remote sql server:
.\run.ps1 Get-TempDbFileBalance -ServerInstance SQLSERVER01
These scripts live in the repo at:
Example Output
Get-TempdbUsage on an 8 CPU lab instance, 9 files:

All 8 data files are the same size, under 5% used, and the version store is close to empty. templog returns NULL in every space column because the DMV only tracks data files.
Get-TempDbFileBalance on the same instance, condensed:
Every check reads OK: 8 logical CPUs, 8 data files, the same size and the same fixed 64 MB growth. Notice size_mb reads 8.0 here and 72.00 in the screenshot. That is not a contradiction: this script reads the size TempDB is recreated at on restart, and the files have grown since.
Understanding the Results
Get-TempdbUsage
pct_usedfree_mbused_mbtempdb.sys.dm_db_file_space_usage. The log row is NULL here by design.Act when pct_used keeps climbing across runs. Find the session using it before the volume fills.version_store_mbREAD_COMMITTED_SNAPSHOT, online index builds and triggers. They are only cleaned up once the oldest transaction that might need them ends.Act when it grows run after run. Look for the oldest open transaction, not at TempDB settings.user_objects_mbinternal_objects_mbsize_mbmax_size_mbauto_growthfile_idlogical_namefile_typephysical_nameGet-TempDbFileBalance
file_count_checkrecommended_files. TOO_FEW means fewer data files than logical CPUs, up to 8. EXCESS means more than 8, which is not wrong on a larger server if the extra files were added in fours to relieve contention that persisted.Act when it reads TOO_FEW. Add data files of the same size until it reads OK.size_balancegrowth_balancesize_mbmax_sizeautogrowthis_percent_growthsys.master_files, which for TempDB holds the size the file is recreated at on restart, not its current size. So a file that autogrew unevenly since the restart still reads OK here: compare size_mb in Get-TempdbUsage for that.logical_cpusrecommended_filesdata_file_countsys.dm_os_sys_info, the target file count (the CPU count, capped at 8) and how many data files exist.file_typefile_idfile_namephysical_nameBest Practices
- Keep every TempDB data file the same size with the same fixed MB growth. Never use percent growth on TempDB.
- Match the data file count to the logical CPU count, up to 8. Beyond 8 CPUs, start at 8 and add files in fours only if allocation contention persists.
- Treat
version_store_mbgrowth as a transaction problem, not a TempDB configuration problem, and find the oldest open transaction. - Re-run
Get-TempDbFileBalanceafter any file resize or addition, to confirm the change restored balance rather than moving the imbalance.
Microsoft Learn: sys.dm_db_file_space_usage · sys.dm_os_sys_info · sys.database_files
Frequently Asked Questions
How is this different from TempDB Configuration and TempDB Hotspots?
TempDB Configuration checks the baseline setup (file paths, initial sizing, trace flags). TempDB Hotspots diagnoses active PAGELATCH allocation-page contention when it’s already happening. These two scripts sit between them: current per-file usage right now, and whether the file setup itself follows the recommended pattern, before contention becomes a live problem.
Why is templog NULL in every space column?
tempdb.sys.dm_db_file_space_usage only returns data files, so the join finds nothing for the log and the columns come back NULL. For log space, use VLF Counts and Transaction Log Size and Usage.
Why do the 2 scripts show different sizes for the same file?
Get-TempdbUsage reads tempdb.sys.database_files, the current size. Get-TempDbFileBalance reads sys.master_files, which for TempDB holds the size each file is recreated at when the service restarts. On the lab instance above, the data files are 72 MB now and go back to 8 MB on restart. To change the restart size, use ALTER DATABASE tempdb MODIFY FILE.
Do these scripts work on Azure SQL Database and Managed Instance?
Get-TempDbFileBalance reads sys.master_files, which Azure SQL Database does not expose, and TempDB files there are managed by the service. Azure SQL Managed Instance and SQL Server 2016 onwards run both scripts. Since SQL Server 2016, setup creates 1 TempDB data file per logical CPU up to 8, so TOO_FEW usually means a server that was upgraded in place or built on an older version.
Summary
TempDB running out and TempDB contending on allocation pages usually start the same way: nobody was watching the per-file numbers until something went wrong. These 2 scripts make that a routine check: how full each file is right now, and whether the files are configured the way Microsoft recommends.
Run both on any inherited server, and re-check after any TempDB file change to confirm it actually restored balance.
Related Scripts
You may also find these scripts useful:
- TempDB Configuration
- TempDB Hotspots
- Lock Escalation, Contention Analysis, and Blocking Chains with Plan
- DBA Scripts: The Complete Guide, the map across every script on this site
Leave a Reply