<# .SYNOPSIS Name: Resize-TransactionLogFile.ps1 The purpose of this script is to identify transaction log files with more VLFs than specified and resize the log files. .DESCRIPTION The purpose of this script is to identify transaction log files with more VLFs than specified and resize the log files. The VLF limit is passed in. If a transction log file's VLF count exceeds the limit and the targetsize parameter is not passed in, the file is resized based on its existing size. If targetsize is passed in, the file will be grown to that size. This function calls DBATools function Expand-DbaDbLogFile which grows the transaction log file in increments of 8192MB. More information about the DBATools suite of PowerShell functions can be found at https://dbatools.io/ .PARAMETER sqlinstance The SQL Server instance to check VLF counts on .PARAMETER vlflimit The VLF limit used to determine if a log file need to be resized .PARAMETER execute Flag to determine if the files should be shrunk or if the VLF counts should be displayed .NOTES Updated: 2019-09-12 Initial build. Release Date: 2019-09-12 Author: Frank Gill, Concurrency, Inc. .EXAMPLE Resize transaction log files with a VLF count greater than 50 Resize-TransactionLogFile -sqlinstance YourInstance -vlfcount 50 -execute 1; .EXAMPLE Display VLF counts for log files with a VLF count greater than 50 Resize-TransactionLogFile -sqlinstance YourInstance -vlfcount 50 -execute 0; .EXAMPLE Resize transaction log files with a VLF count greater than 50 to 512MB Resize-TransactionLogFiles -sqlinstance fbgsql2019vm1 -vlflimit 19 -execute 1 -targetsize 512; .EXAMPLE Resize transaction log files with a VLF count greater than 50 to 512MB, writing log file backup to the backup path specified Resize-TransactionLogFiles -sqlinstance fbgsql2019vm1 -vlflimit 19 -execute 1 -targetsize 512 -backuppath "Z:\backup"; #> Function Resize-TransactionLogFiles{ [CmdletBinding()] PARAM ( [Parameter(Mandatory=$true)] [string] $sqlinstance, [Parameter(Mandatory=$true)] [int] $vlflimit, [Parameter(Mandatory=$false)] [int] $execute = 0, [Parameter(Mandatory=$false)] [int] $shrinksize = 1, [Parameter(Mandatory=$false)] [int] $targetsize = 0, [Parameter(Mandatory=$false)] [string] $backuppath ) Begin{ Write-Host "Start Resize-TransactionLogFiles function..." -ForegroundColor Yellow; } Process{ Try{ $highvlfs = @(); $highvlfs = Get-DbaDbVirtualLogFile -SqlInstance $sqlinstance | Group-Object -Property Database | Where-Object Count -gt $vlflimit; $length = $highvlfs.Length; $dbfileinfo = Get-DbaDbLogSpace -SqlInstance $sqlinstance -ExcludeSystemDatabase; foreach($highvlf in $highvlfs) { $highvlfname = $highvlf.Name; foreach($dbfile in $dbfileinfo) { $dbfilename = $dbfile.Database; $filesize = [math]::Round($dbfile.LogSize.Megabyte); if($highvlfname -eq $dbfilename) { if($targetsize -eq 0) { if($filesize -le 1024) { $targetsize = 1024 } elseif($filesize -le 4096) { $targetsize = 4096 } elseif($filesize -le 8192) { $targetsize = 8192 } elseif($filesize -gt 8192) { $targetsize = $filesize } } Write-Host "File size is $filesize and target size is $targetsize"; if(($execute -eq 1) -and ($filesize -ne $targetsize)) { Expand-DbaDbLogFile -SqlInstance $sqlinstance -Database $dbfilename -TargetLogSize $targetsize -ShrinkSize $shrinksize -ShrinkLogFile -BackupDirectory $backuppath; } elseif ($execute -eq 1) { throw "Current file size and target file size are equal. Current file size has been maintained." } } } } if($execute -eq 0) { $highvlfs | Format-Table -Property Name, Count; } if($length -eq 0) { throw "No transaction log file has a VLF count greater than the VLF limit of $vlflimit"; } } Catch{ Write-Host "$($PSItem.ToString())" -ForegroundColor Red; Break } } End{ If($?){ # only execute if the function was successful. Write-Host "Completed Resize-TransactionLogFile function." -ForegroundColor Yellow; } } }