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.
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
$File.Alter()
}
}
#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
$LogFile.Alter()
}
}
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”.
