Set-DbaDbFileGrowth

Synopsis

Modifies auto-growth settings for database data and log files to use fixed-size increments instead of percentage-based growth.

Description

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.

Syntax

Set-DbaDbFileGrowth
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <String[]>]
    [[-GrowthType] <String>]
    [[-Growth] <Int32>]
    [[-FileType] <String>]
    [[-InputObject] <Database[]>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Set-DbaDbFileGrowth -SqlInstance sql2016 -Database test  -GrowthType GB -Growth 1

Sets the test database on sql2016 to a growth of 1GB

Example: 2

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

Example: 3

PS C:\> Get-DbaDatabase | Set-DbaDbFileGrowth -SqlInstance sql2017, sql2016, sql2012

Sets all database files on sql2017, sql2016, sql2012 to 64MB.

Example: 4

PS C:\> Set-DbaDbFileGrowth -SqlInstance sql2017, sql2016, sql2012 -Database test -WhatIf

Shows what would happen if the command were executed

Example: 5

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

Optional Parameters

-SqlInstance

The target SQL Server instance or instances.

-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.

-Database

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.

-GrowthType

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.

-Growth

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.

-FileType

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.

-InputObject

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.

-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