< Back
Set-DbaTempDbConfig
Post
NAME Set-DbaTempDbConfig
SYNOPSIS
Sets tempdb data and log files according to best practices.
SYNTAX
Set-DbaTempDbConfig [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [[-SqlCredential]
<Pscredential>] [[-DataFileCount] <Int>] [-DataFileSize] <Int> [[-LogFileSize] <Int>] [[-DataFileGrowth] <Int>]
[[-LogFileGrowth] <Int>] [[-DataPath] <String>] [[-LogPath] <String>] [[-OutFile] <String>] [-OutputScriptOnly
<Switch>] [-DisableGrowth <Switch>] [-EnableException <Switch>] [<CommonParameters>]
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, with a log file 25% of the total data file size.
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.
PARAMETERS
-DataFileCount [<Int>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DataFileGrowth [<Int>]
Specifies the growth amount for the data file(s) in megabytes. The default is 512 MB.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DataFileSize [<Int>]
Specifies the total data file size in megabytes. This is distributed across the total number of data files.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DataPath [<String>]
Specifies the filesystem path in which to create the tempdb data files. If not specified, current tempdb
location will be used.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DisableGrowth [<Switch>]
If this switch is enabled, the tempdb files will be configured to not grow. This overrides -DataFileGrowth and
-LogFileGrowth.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-LogFileGrowth [<Int>]
Specifies the growth amount for the log file in megabytes. The default is 512 MB.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-LogFileSize [<Int>]
Specifies the log file size in megabytes. If not specified, this will be set to 25% of total data file size.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-LogPath [<String>]
Specifies the filesystem path in which to create the tempdb log file. If not specified, current tempdb
location will be used.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-OutFile [<String>]
Specifies the filesystem path into which the generated T-SQL script will be saved.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-OutputScriptOnly [<Switch>]
If this switch is enabled, only the T-SQL script to change the tempdb configuration is created and output.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Tempdb, Configuration
Author: Michael Fal (@Mike_Fal), http://mikefal.net
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- 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.
RELATED LINKS
https://dbatools.io/Set-DbaTempDbConfig
SYNOPSIS
Sets tempdb data and log files according to best practices.
SYNTAX
Set-DbaTempDbConfig [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [[-SqlCredential]
<Pscredential>] [[-DataFileCount] <Int>] [-DataFileSize] <Int> [[-LogFileSize] <Int>] [[-DataFileGrowth] <Int>]
[[-LogFileGrowth] <Int>] [[-DataPath] <String>] [[-LogPath] <String>] [[-OutFile] <String>] [-OutputScriptOnly
<Switch>] [-DisableGrowth <Switch>] [-EnableException <Switch>] [<CommonParameters>]
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, with a log file 25% of the total data file size.
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.
PARAMETERS
-DataFileCount [<Int>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DataFileGrowth [<Int>]
Specifies the growth amount for the data file(s) in megabytes. The default is 512 MB.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DataFileSize [<Int>]
Specifies the total data file size in megabytes. This is distributed across the total number of data files.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DataPath [<String>]
Specifies the filesystem path in which to create the tempdb data files. If not specified, current tempdb
location will be used.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DisableGrowth [<Switch>]
If this switch is enabled, the tempdb files will be configured to not grow. This overrides -DataFileGrowth and
-LogFileGrowth.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-LogFileGrowth [<Int>]
Specifies the growth amount for the log file in megabytes. The default is 512 MB.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-LogFileSize [<Int>]
Specifies the log file size in megabytes. If not specified, this will be set to 25% of total data file size.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-LogPath [<String>]
Specifies the filesystem path in which to create the tempdb log file. If not specified, current tempdb
location will be used.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-OutFile [<String>]
Specifies the filesystem path into which the generated T-SQL script will be saved.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-OutputScriptOnly [<Switch>]
If this switch is enabled, only the T-SQL script to change the tempdb configuration is created and output.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Tempdb, Configuration
Author: Michael Fal (@Mike_Fal), http://mikefal.net
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- 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.
RELATED LINKS
https://dbatools.io/Set-DbaTempDbConfig