<#
.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;
    }
  }
}