Change SQL Server Database Initial Size and Auto Growth Settings using PowerShell

Requirement: After a new farm deployment, I had to raise the size and growth of all SharePoint content databases. There were 20+ databases in the initial setup.

As per SQL Server best practices for SharePoint, we must set the SharePoint content databases’ initial size and growth properties for better performance, which can be configured from database properties on each content database.

Set SQL Database initial size and autogrowth settings using PowerShell

PowerShell to Set SharePoint Database Initial Size and Auto Growth Settings:

We can use PowerShell to configure database properties with many content databases.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Import SQL PowerShell module
Import-Module SQLPS -DisableNameChecking

#Get All SharePoint Content Databases
$ContentDatabases = Get-SPContentDatabase 

#Loop through each content database
ForEach($Database in $ContentDatabases)
    #Get the SharePoint content database in SQL Server
    $SQLDatabase = Get-SqlDatabase -Name $Database.Name -ServerInstance $Database.Server

    #Set Size and Autogrowth Settings for Data Files of the Database
    $FileGroups = $SQLDatabase.FileGroups
    ForEach($FileGroup in $FileGroups)
        ForEach ($File in $FileGroup.Files)
            $File.GrowthType = "KB"
            $File.Growth = "512000" #500MB
            $File.MaxSize = "-1" #Unlimited
            $File.Size = "5242880" #5GB

    #Set Size and Autogrowth Settings for Log Files
    Foreach($LogFile in $SQLDatabase.LogFiles)
        $LogFile.GrowthType = [Microsoft.SqlServer.Management.Smo.FileGrowthType]::KB
        $LogFile.Growth = "102400"  #100MB
        $LogFile.Size = "2097152" #2GB
        $LogFile.MaxSize = "-1" #Unlimited

Make sure you have enough disk space on the server before increasing the size value. Also, the Initial size value must be higher than the current size of the database file. Otherwise, you may run into the error “Exception calling “Alter” with “0” argument(s): “Alter failed for DataFile/LogFile”.

Salaudeen Rajack

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

Leave a Reply

Your email address will not be published. Required fields are marked *