DBA Scripts: Get TempDB Usage and File Balance

---

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 two 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, one per logical CPU up to 8, before contention ever becomes a problem.

---

Why TempDB Usage and File Balance Matter

• TempDB filling up stops every session on the instance that needs temp space, not just one database, a shared, instance-wide resource with no per-database isolation
• Unequal file sizes defeat SQL Server’s proportional-fill allocation algorithm, new allocations skew toward whichever file has the most free space, concentrating contention right back onto fewer files
• version_store_mb growing unexpectedly is a specific, useful signal: it means a long-running transaction (often READ_COMMITTED_SNAPSHOT or an open transaction) is holding row versions that can’t be cleaned up yet
• File count and CPU count alignment is the single most impactful TempDB setting for reducing allocation-page contention, and it’s set once at install time and then forgotten

---

When to Run These Scripts

• Get-TempdbUsage — when investigating TempDB growth, a “TempDB is full” error, or unexpected disk pressure on the TempDB volume
• Get-TempDbFileBalance — 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 rather than making an existing imbalance worse
• Alongside TempDB Hotspots when investigating PAGELATCH contention on allocation pages

---

The Scripts

1. Get-TempdbUsage — Per-File Size, Free Space, and Allocation Breakdown

/*
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
*/
-- Blog: https://sqldba.blog/dba-scripts-get-temp-db-usage-and-file-balance/
-- 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
*/
-- Blog: https://sqldba.blog/dba-scripts-get-temp-db-usage-and-file-balance/
-- 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:

• sql/monitoring/tempdb/Get-TempdbUsage.sql
• sql/monitoring/tempdb/Get-TempDbFileBalance.sql

---

Example Output

Real output from this lab instance, not staged.

Get-TempdbUsage (9 files, condensed here):

logical_name	file_type	size_mb	free_mb	user_objects_mb	pct_used
tempdev	ROWS	136.00	132.94	1.38	2.25
temp2	ROWS	136.00	135.94	0.00	0.05
temp7	ROWS	136.00	135.50	0.00	0.37
templog	LOG	8.00	(not tracked for log files)

Get-TempDbFileBalance (9 files, all healthy on this lab box):

file_type	size_mb	autogrowth	logical_cpus	recommended_files	data_file_count	file_count_check	size_balance	growth_balance
ROWS	8.0	64 MB	8	8	8	OK	OK	OK

This instance is correctly configured: 8 logical CPUs, 8 data files, all the same size, all the same fixed 64 MB growth, no percent-based growth anywhere.

---

Understanding the Results

• pct_used climbing toward 100% — TempDB is a shared resource, running out affects every session on the instance, not just one workload; investigate the source (a large sort/hash operation, an open long-running transaction) before it becomes an outage
• version_store_mb growing — a specific, actionable signal: something is holding row versions open, usually a long-running transaction under READ_COMMITTED_SNAPSHOT or an explicit transaction left open; find and address the source transaction directly
• file_count_check = TOO_FEW — fewer data files than logical CPUs (capped at 8) increases allocation-page contention risk; add files to match, up to 8
• size_balance = IMBALANCED or growth_balance = IMBALANCED/PCT_GROWTH — proportional-fill allocation will skew toward the larger or faster-growing file, concentrating exactly the contention that having multiple files was meant to spread out; fix by resizing all data files to match and setting identical fixed-MB growth

---

Best Practices

• Keep all TempDB data files the same size and the same fixed-MB growth setting, never percent-based growth on TempDB
• Match data file count to logical CPU count, capped at 8, the standard, well-tested Microsoft recommendation
• Investigate version_store_mb growth immediately, it points directly at a long-running transaction that needs addressing, not a TempDB configuration problem
• Re-run Get-TempDbFileBalance after any manual file resize or addition, confirm the change actually restored balance rather than shifting the imbalance elsewhere

---

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

---

Frequently Asked Questions

Summary

TempDB running out or contending on allocation pages both start the same way: nobody was watching the per-file numbers until something already went wrong. These two scripts turn that into a routine check, exactly how full each file is right now, and whether the files themselves are configured the way Microsoft’s own guidance recommends.

Run both on any inherited server, and re-check after any TempDB file change to confirm it actually restored balance.