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+ in the initial setup.
As per SQL Server best practices for SharePoint, we must set the initial size and growth properties of the SharePoint content databases 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:
When we have many content databases, we can utilize PowerShell to configure database properties.
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 error “Exception calling “Alter” with “0” argument(s): “Alter failed for DataFile/LogFile”
