Set-DbaTempDbConfig

Want to see the source code for this command? Check out Set-DbaTempDbConfig on GitHub.
Want to see the Bill Of Health for this command? Check out Set-DbaTempDbConfig.

Synopsis

Configures tempdb database files according to Microsoft best practices for optimal performance

Description

Configures tempdb database files to follow Microsoft's recommended best practices for performance optimization. This function calculates the optimal number of data files based on logical CPU cores (capped at 8) and distributes the specified total data file size evenly across those files. You must specify the target SQL Server instance and total data file size as mandatory parameters.

The function automatically determines the appropriate number of data files based on your server's logical cores, but you can override this behavior. It validates the current tempdb configuration to ensure it won't conflict with your desired settings - existing files must be smaller than the calculated target size and you cannot have more existing files than the target configuration.

Additional parameters let you customize file paths, log file size, and growth settings. The function generates ALTER DATABASE statements but does not shrink or delete existing files. If your current tempdb is larger than your target configuration, you'll need to shrink it manually before running this function. A SQL Server restart is required for tempdb changes to take effect.

Syntax

Set-DbaTempDbConfig
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-DataFileCount] <Int32>]
    [-DataFileSize] <Int32>
    [[-LogFileSize] <Int32>]
    [[-DataFileGrowth] <Int32>]
    [[-LogFileGrowth] <Int32>]
    [[-DataPath] <String[]>]
    [[-LogPath] <String>]
    [[-OutFile] <String>]
    [-OutputScriptOnly]
    [-DisableGrowth]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Set-DbaTempDbConfig -SqlInstance localhost -DataFileSize 1000

Creates tempdb with a number of data files equal to the logical cores where each file is equal to 1000MB divided by the number of logical cores, with a log file of 250MB.

Example: 2

PS C:\> Set-DbaTempDbConfig -SqlInstance localhost -DataFileSize 1000 -DataFileCount 8

Creates tempdb with 8 data files, each one sized at 125MB, with a log file of 250MB.

Example: 3

PS C:\> Set-DbaTempDbConfig -SqlInstance localhost -DataFileSize 1000 -OutputScriptOnly

Provides a SQL script output to configure tempdb according to the passed parameters.

Example: 4

PS C:\> Set-DbaTempDbConfig -SqlInstance localhost -DataFileSize 1000 -DisableGrowth

Disables the growth for the data and log files.

Example: 5

PS C:\> Set-DbaTempDbConfig -SqlInstance localhost -DataFileSize 1000 -OutputScriptOnly

Returns the T-SQL script representing tempdb configuration.

Required Parameters

-SqlInstance

The target SQL Server instance or instances.

-DataFileSize

Sets the total size in MB for all tempdb data files combined. This value gets evenly divided across all data files.
For example, 1000MB with 4 files creates four 250MB files. Choose based on your workload's tempdb usage patterns and available storage.

Optional Parameters

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-DataFileCount

Sets the number of tempdb data files to create. When omitted, automatically uses the logical core count (capped at 8 per Microsoft best practices).
Override this when you need a specific file count different from core count, though exceeding core count generates a warning as it goes against best practices.

-LogFileSize

Sets the tempdb log file size in MB. When omitted, the existing log file size remains unchanged.
Use this to resize the log file when current sizing doesn't match your tempdb transaction volume requirements.

-DataFileGrowth

Controls the growth increment for tempdb data files in MB when they need to expand. Defaults to 512 MB.
Set this based on your typical tempdb usage spikes to avoid frequent small growths that can impact performance. Use 0 with -DisableGrowth to prevent growth entirely.

-LogFileGrowth

Controls the growth increment for the tempdb log file in MB when it needs to expand. Defaults to 512 MB.
Size this according to your transaction log activity in tempdb to minimize auto-growth events during peak workloads.

-DataPath

Sets the folder path(s) where tempdb data files will be created. When omitted, uses the current tempdb data file location.
Specify multiple paths to distribute files across different drives for performance. Files are distributed round-robin across the provided paths.

-LogPath

Sets the folder path where the tempdb log file will be created. When omitted, uses the current tempdb log file location.
Consider placing the log file on a separate drive from data files to reduce I/O contention for write-heavy tempdb workloads.

-OutFile

Saves the generated T-SQL script to the specified file path instead of executing it.
Useful for storing configuration scripts in source control or running them later through scheduled maintenance processes.

-OutputScriptOnly

Returns the generated T-SQL script without executing it against the SQL Server instance.
Use this to review the configuration changes before applying them, or to run the script manually during maintenance windows.

-DisableGrowth

Prevents tempdb files from auto-growing by setting growth to 0. Overrides any values specified for -DataFileGrowth and -LogFileGrowth.
Use this when you want to pre-size tempdb files appropriately and prevent unexpected growth during production workloads.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

-WhatIf

If this switch is enabled, no actions are performed but informational messages will be displayed that explain what would happen if the command were to run.

-Confirm

If this switch is enabled, you will be prompted for confirmation before executing any operations that change state.