DBA Scripts: Get TempDB Usage and File Balance

🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.In: Storage & Capacity

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_mb growing is a specific signal: a long-running transaction under snapshot isolation or READ_COMMITTED_SNAPSHOT is 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


The Scripts

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

✓ Verified
  • 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:

SSMS running Get-TempdbUsage on an 8 CPU lab instance: 8 tempdb data files, tempdev and temp2 to temp8, each 72.00 MB with 64 MB growth and between 68.75 and 71.75 MB free, pct_used from 0.35 to 4.51, version_store_mb 0.06 or less, then templog at 1096.00 MB with NULL in every space column.

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:

Get-TempDbFileBalanceoutput of a real run, columns trimmed
file_type file_name size_mb autogrowth logical_cpus data_file_count file_count_check size_balance growth_balance ROWS tempdev 8.0 64 MB 8 8 OK OK OK ROWS temp2 8.0 64 MB 8 8 OK OK OK ... temp3 to temp8 identical LOG templog 1096.0 64 MB 8 8 OK OK OK

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_used
free_mb
used_mb
How full each data file is right now, read from tempdb.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_mb
Row versions kept for snapshot isolation, READ_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_mb
internal_objects_mb
User objects are temp tables and table variables. Internal objects are sorts, hash joins and spools spilling to TempDB. A large internal figure points at a query plan, not at application code.
size_mb
max_size_mb
auto_growth
The current size of each file, its cap (NULL means unlimited) and its growth step. Current size includes any autogrowth since the last restart.
file_id
logical_name
file_type
physical_name
Which file the row is. Data files are ROWS, the log is LOG. Rows are ordered data files first, then the log.

Get-TempDbFileBalance

file_count_check
Compares the data file count with recommended_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_balance
growth_balance
IMBALANCED means the data files differ in configured size or growth step. PCT_GROWTH means a data file grows by a percentage, so its steps get larger as it grows and the files drift apart.Act when either is not OK. Resize every data file to match and set the same fixed MB growth.
size_mb
max_size
autogrowth
is_percent_growth
Read from sys.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_cpus
recommended_files
data_file_count
The CPU count from sys.dm_os_sys_info, the target file count (the CPU count, capped at 8) and how many data files exist.
file_type
file_id
file_name
physical_name
Which file the row is. The checks describe the data files as a set, so the same value repeats on every row, including the log row.

Best 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_mb growth as a transaction problem, not a TempDB configuration problem, and find the oldest open transaction.
  • Re-run Get-TempDbFileBalance after 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

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *