Author | Chrissy LeMaire (@cl), netnerds.net |
Availability | Windows, Linux, macOS |
Want to see the source code for this command? Check out Set-DbaDbFileGrowth on GitHub.
Want to see the Bill Of Health for this command? Check out Set-DbaDbFileGrowth.
Modifies auto-growth settings for database data and log files to use fixed-size increments instead of percentage-based growth.
Configures database file auto-growth settings using ALTER DATABASE statements to replace default percentage-based growth with fixed-size increments. This prevents unpredictable growth patterns that can cause performance issues and storage fragmentation as databases grow larger. Defaults to 64MB growth increments, which provides better control over file expansion and reduces the risk of exponential growth that can quickly consume available disk space. You can target specific file types (data files, log files, or both) and specify custom growth values in KB, MB, GB, or TB units.
Set-DbaDbFileGrowth
[[-SqlInstance] <DbaInstanceParameter[]>]
[[-SqlCredential] <PSCredential>]
[[-Database] <String[]>]
[[-GrowthType] <String>]
[[-Growth] <Int32>]
[[-FileType] <String>]
[[-InputObject] <Database[]>]
[-EnableException]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
PS C:\> Set-DbaDbFileGrowth -SqlInstance sql2016 -Database test -GrowthType GB -Growth 1
Sets the test database on sql2016 to a growth of 1GB
PS C:\> Get-DbaDatabase -SqlInstance sql2016 -Database test | Set-DbaDbFileGrowth -GrowthType GB -Growth 1
Sets the test database on sql2016 to a growth of 1GB
PS C:\> Get-DbaDatabase | Set-DbaDbFileGrowth -SqlInstance sql2017, sql2016, sql2012
Sets all database files on sql2017, sql2016, sql2012 to 64MB.
PS C:\> Set-DbaDbFileGrowth -SqlInstance sql2017, sql2016, sql2012 -Database test -WhatIf
Shows what would happen if the command were executed
PS C:\> Set-DbaDbFileGrowth -SqlInstance sql2017 -Database test -GrowthType GB -Growth 1 -FileType Data
Sets growth to 1GB for only data files for database test
The target SQL Server instance or instances.
Alias | |
Required | False |
Pipeline | false |
Default Value |
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 |
Specifies which databases to modify file growth settings for. Accepts an array of database names.
Use this when you need to target specific databases rather than all databases on an instance.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Specifies the unit of measurement for the growth increment. Valid values are KB, MB, GB, or TB.
Choose the appropriate unit based on your database size and expected growth patterns - MB for smaller databases, GB for larger ones.
Alias | |
Required | False |
Pipeline | false |
Default Value | MB |
Accepted Values | KB,MB,GB,TB |
Sets the numeric value for the fixed growth increment. Defaults to 64 when combined with the default MB unit.
Use smaller values (16-64MB) for smaller databases or larger values (256MB-1GB) for high-growth production databases to balance performance and storage efficiency.
Alias | |
Required | False |
Pipeline | false |
Default Value | 64 |
Controls which file types to modify - Data files only, Log files only, or All files (both data and log).
Use 'Data' when you need different growth settings for data vs log files, or 'All' to standardize growth across all database files.
Alias | |
Required | False |
Pipeline | false |
Default Value | All |
Accepted Values | All,Data,Log |
Accepts database objects from Get-DbaDatabase for pipeline operations.
Use this when you need to filter databases first or when working with database objects from other dbatools functions.
Alias | |
Required | False |
Pipeline | true (ByValue) |
Default Value |
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 |
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 |
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 |