commands

^

Set-DbaTempDbConfig

Author Michael Fal (@Mike_Fal), http://mikefal.net
Availability Windows, Linux, macOS

 

Synopsis

Sets tempdb data and log files according to best practices.

Description

Calculates tempdb size and file configurations based on passed parameters, calculated values, and Microsoft best practices. User must declare SQL Server to be configured and total data file size as mandatory values. Function then calculates the number of data files based on logical cores on the target host and create evenly sized data files based on the total data size declared by the user.

Other parameters can adjust the settings as the user desires (such as different file paths, number of data files, and log file size). No functions that shrink or delete data files are performed. If you wish to do this, you will need to resize tempdb so that it is "smaller" than what the function will size it to before running the function.

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.

Alias
Required True
Pipeline false
Default Value
-DataFileSize

Specifies the total data file size in megabytes. This is distributed across the total number of data files.

Alias
Required True
Pipeline false
Default Value 0

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.

Alias
Required False
Pipeline false
Default Value
-DataFileCount

Specifies the number of data files to create. If this number is not specified, the number of logical cores of the host will be used.

Alias
Required False
Pipeline false
Default Value 0
-LogFileSize

Specifies the log file size in megabytes. If not specified, no change will be made.

Alias
Required False
Pipeline false
Default Value 0
-DataFileGrowth

Specifies the growth amount for the data file(s) in megabytes. The default is 512 MB.

Alias
Required False
Pipeline false
Default Value 512
-LogFileGrowth

Specifies the growth amount for the log file in megabytes. The default is 512 MB.

Alias
Required False
Pipeline false
Default Value 512
-DataPath

Specifies the filesystem path(s) in which to create the tempdb data files. If not specified, current tempdb location will be used.

Alias
Required False
Pipeline false
Default Value
-LogPath

Specifies the filesystem path in which to create the tempdb log file. If not specified, current tempdb location will be used.

Alias
Required False
Pipeline false
Default Value
-OutFile

Specifies the filesystem path into which the generated T-SQL script will be saved.

Alias
Required False
Pipeline false
Default Value
-OutputScriptOnly

If this switch is enabled, only the T-SQL script to change the tempdb configuration is created and output.

Alias
Required False
Pipeline false
Default Value False
-DisableGrowth

If this switch is enabled, the tempdb files will be configured to not grow. This overrides -DataFileGrowth and -LogFileGrowth.

Alias
Required False
Pipeline false
Default Value False
-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.

Alias
Required False
Pipeline false
Default Value False
-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.

Alias wi
Required False
Pipeline false
Default Value
-Confirm

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

Alias cf
Required False
Pipeline false
Default Value

 

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.