· Uwe Janke
Commands / Set-sqmTempDbFileSize

Set-sqmTempDbFileSize

Performance sqmSQLTool v1.9.206.0+ · Set
Brings all TempDB data files to the same size. SQL Server fills files in proportion to their free space (proportional fill), so one oversized file takes most of the load and the additional files do not relieve the allocation pages as intended. The function reads the current size of every data file and grows the smaller ones to the target size.

The target is the largest existing file unless -TargetSizeMB asks for more. ALTER DATABASE ... MODIFY FILE can only grow a file, so nothing that has already grown is lost. The change is online and is stored as the startup size, so it survives a restart. The log file is not touched. Shrinking is deliberately not part of this function: DBCC SHRINKFILE on TempDB can fail under load and often needs a restart.

Before the first change it checks that no file has a MAXSIZE below the target and that every volume has enough free space. If either check fails, nothing is changed, so TempDB is never left half equalized. Without VIEW SERVER STATE the space check is skipped with a warning.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptionallocal computer nameSQL Server instance.
-SqlCredentialPSCredentialOptional—Optional SQL login.
-TargetSizeMBintOptionallargest data fileTarget size per data file in MB. Must not be below the largest file, because files are not shrunk here.
-FileGrowthMBintOptionalunchangedSets the same fixed autogrowth step on all data files. Without it, differing steps are only reported: the files drift apart again at the next growth.
-WhatIf / -ConfirmswitchSwitch—Shows or confirms each file change.
-EnableExceptionswitchSwitch$falseThrow instead of logging and returning a failed result object.

Return Value

One PSCustomObject per data file: SqlInstance, LogicalName, PhysicalName, OldSizeMB, NewSizeMB, OldGrowth, NewGrowth, Status (Changed, Unchanged, WhatIf or Failed) and Message.

Execution Flow

START Read the data files tempdb.sys.database_files, type ROWS: current size, MAXSIZE, growth Determine the target size largest file, or -TargetSizeMB (not below the largest file) Pre-checks, before any change MAXSIZE below target? free space per volume enough? If not: abort, nothing changed Grow each smaller file ALTER DATABASE tempdb MODIFY FILE (SIZE), one property per statement Optional: set autogrowth (-FileGrowthMB) ALTER DATABASE tempdb MODIFY FILE (FILEGROWTH), separate statement DONE

Examples

Show what would change, without changing anything
Set-sqmTempDbFileSize -SqlInstance "SQL01" -WhatIf
Bring all data files to the size of the largest one
Set-sqmTempDbFileSize -SqlInstance "SQL01"
Fixed target size and one autogrowth step for every file
Set-sqmTempDbFileSize -SqlInstance "SQL01" -TargetSizeMB 8192 -FileGrowthMB 512
← Back
Command Index
Related
Test-sqmTempDbFileCount
Related
Get-sqmTempDbRecommendation