Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Function to set the log file initial size and growth values for all databases on an instance to 512MB
Function Set-SQLLogFileSizeAndGrowth
{
Param
(
$Instance
)
Import-Module dbatools -ErrorAction SilentlyContinue
If ((Get-Module | Where-Object {$_.Name -eq "dbatools"}) -eq $null)
{
Write-Output "Installing required module: $dbatools"
install-module dbatools -Force
}
Else
{
Write-Output "dbatools module loaded."
}
# Get all the databases on an instance, excluding the system databases
$Databases = Get-DbaDatabase -SqlInstance $Instance -NoSystemDb
$LogSize = "51200"
$LogGrowthType = "KB"
$LogGrowth = "51200"
foreach ($DB in $databases)
{
$Database = $DB.Name
Write-Output "$(Get-Date) $Instance $Database Updating log file initial size and growth to recommended values"
foreach ($DBLF in $DB.logfiles)
{
$LogFileInitalize = $DBLF.Size
$LogFileGrowth = $DBLF.Growth
Write-Output "$(Get-Date) $Instance $Database Current Initial Size: $LogFileInitalize Current Growth Size: $LogFileGrowth"
If ($LogFileInitalize -ne $LogSize)
{
Try
{
$DBLF.Size = $LogSize
$DBLF.Alter | Out-Null
Write-Output "$(Get-Date) $Instance $Database Log Initial Size Update Complete"
}
Catch
{
Write-Output "$(Get-Date) $Instance $Database Log Initial Size Update Failed"
}
}
Else
{
Write-Output "$(Get-Date) $Instance $Database Log Initial Size No Change Needed"
}
If ($LogFileGrowth -ne $LogGrowth)
{
Try
{
$DBLF.GrowthType = $LogGrowthType
$DBLF.Growth = $LogGrowth
$DBLF.Alter | Out-Null
Write-Output "$(Get-Date) $Instance $Database Log Growth Update Complete"
}
Catch
{
Write-Output "$(Get-Date) $Instance $Database Log Growth Update Failed"
}
}
Else
{
Write-Output "$(Get-Date) $Instance $Database Log Growth No Change Needed"
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.