Set-sqmTempDbFileCount
Performance
sqmSQLTool v1.9.215+ · Set
✓ -WhatIf supported
Test-sqmTempDbFileCount: the test reports that the number of data files does not match the logical cores, this function fixes it.
How many. Without
-FileCount the same recommendation applies as in the test: the smaller of the logical cores of the target instance and -MaxFiles (default 8, or the configuration value CheckTempDbMaxFiles).
The new files are created online with
ALTER DATABASE tempdb ADD FILE, no restart needed. They get the size of the largest existing data file, because proportional fill distributes unevenly otherwise, the same fixed growth step as the first file (64 MB if that one grows by percent), and they live in the folder of the first data file or in -Path. Names follow the installation: temp2, temp3 with tempdb_mssql_2.ndf, tempdb_mssql_3.ndf. Names and paths already in use are skipped.
Safety. Before the first change the function checks that the target volume has room for all new files. If not, it aborts without changing anything. Removing files is deliberately not supported: a used TempDB file cannot be emptied reliably online (
DBCC SHRINKFILE ... EMPTYFILE fails under load, often only a restart helps). If there are already more files than wanted, the result is Skipped.
Parameters
| Parameter | Type | Required | Default | Notes |
|---|---|---|---|---|
| -SqlInstance | string | Optional | $env:COMPUTERNAME | SQL Server instance. |
| -SqlCredential | PSCredential | Optional | , | SQL authentication. |
| -FileCount | int (1-64) | Optional | cores, max 8 | Wanted number of data files. Without it: min(logical cores, -MaxFiles). |
| -MaxFiles | int (1-64) | Optional | 8 | Upper limit for the automatic recommendation (or configuration CheckTempDbMaxFiles). Has no effect on an explicit -FileCount. |
| -Path | string | Optional | folder of first file | Folder for the new files, a path on the SQL Server. Must exist and be writable for the service account. |
| -FileGrowthMB | int (1-65536) | Optional | like first file | Autogrowth step of the new files in MB. Default: like the first file, 64 MB for percent growth. |
| -EqualizeSize | switch | Switch | $false | After creating, bring all data files to the same size through Set-sqmTempDbFileSize. |
| -EnableException | switch | Switch | $false | Throw instead of logging and returning an error object. |
| -WhatIf / -Confirm | switch | Optional | , | Shows the files that would be created together with the T-SQL. |
Execution Flow
Return value
One object per new file with SqlInstance, LogicalName, PhysicalName, SizeMB, GrowthMB, OldFileCount, NewFileCount, Status and Message. If there is nothing to do, exactly one object with status Skipped.
| Status | Meaning |
|---|---|
Created | The file was added. |
WhatIf | -WhatIf: the message holds the statement that would run. |
Skipped | The instance already has the wanted number of files, or more. |
Failed | Adding the file failed (the message names the reason). Remaining files are not tried. |
Needs ALTER ANY DATABASE (sysadmin). The space check uses sys.dm_os_volume_stats and therefore VIEW SERVER STATE; without it the check is skipped with a warning.
Examples
Which files would be created
Set-sqmTempDbFileCount -SqlInstance "SQL01" -WhatIf
Create the missing files and equalise all sizes
Set-sqmTempDbFileCount -SqlInstance "SQL01" -EqualizeSize
Fixed count and a dedicated folder
Set-sqmTempDbFileCount -SqlInstance "SQL01" -FileCount 8 -Path "T:\TempDB"
Test first, then fix
if ((Test-sqmTempDbFileCount -SqlInstance "SQL01" -NoOpen).Status -ne "OK") {
Set-sqmTempDbFileCount -SqlInstance "SQL01" -EqualizeSize
}