Functions/Invoke-SqlDbTrxShrink.ps1
|
<#
.SYNOPSIS Shrink a transaction log file of a SQL Server Database. After shrinking, the file will be expandet to the target size. .DESCRIPTION .LINK https://github.com/claudiospizzi/SqlServerFever #> function Invoke-SqlDbTrxShrink { [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'High')] param ( # SQL instance name. [Parameter(Mandatory = $true)] [System.String] $SqlInstance, # SQL Login. If not specified, use integrated security. [Parameter(Mandatory = $false)] [ValidateNotNull()] [System.Management.Automation.PSCredential] $SqlCredential, # Database to connect, be default master. [Parameter(Mandatory = $true)] [System.String] $Database, # The target size of the transaction log file. [Parameter(Mandatory = $true)] [System.Int64] $TargetSize, # Database where SQL Maintenance Solution Jobs by Ola Hallengren are # stored in. [Parameter(Mandatory = $false)] [System.String] $MaintenanceSolutionDatabase = 'DBATools', # Validity time for the transaction log backup created by the SQL # Maintenance Solution. [Parameter(Mandatory = $false)] [System.Int32] $MaintenanceSolutionCleanupTime = 72, # Auto-shrink to the target size. [Parameter(Mandatory = $false)] [Switch] $Auto ) # # Update target size for SQL Server. # $TargetSize = $TargetSize / 1024 * 1000 # Define and verify the connection splat to the SQL Server. $sqlConnection = @{ SqlInstance = $SqlInstance Database = $Database } if ($PSBoundParameters.ContainsKey('SqlCredential')) { $sqlConnection['SqlCredential'] = $SqlCredential } Test-SqlConnection @sqlConnection -Verbose:$false | Out-Null $databaseLogFileName = Get-DbaDbFile @sqlConnection -Verbose:$false | Where-Object { $_.Type -eq 1 } | Select-Object -First 1 -ExpandProperty 'LogicalName' # Prepare the transaction log backup query. $queryBackup = "EXECUTE [{0}].[dbo].[DatabaseBackup] @Databases = '{1}', @BackupType = 'LOG', @Compress = 'Y', @Verify = 'Y', @CleanupTime = {2}, @CheckSum = 'Y', @LogToTable = 'Y'" -f $MaintenanceSolutionDatabase, $Database, $MaintenanceSolutionCleanupTime $queryShrink = "DBCC SHRINKFILE('{0}', 1)" -f $databaseLogFileName $queryResize = "ALTER DATABASE [{0}] MODIFY FILE ( NAME = N'{1}', SIZE = {2}KB )" -f $Database, $databaseLogFileName, (($TargetSize / 1KB) -as [System.Int64]) # Get and show the transaction log state $state = Get-SqlDbTrxLogState @sqlConnection Write-Output $state while (($state.FileSize -gt $TargetSize -or $state.VlfCount -gt 8) -and ($Auto.IsPresent -or $PSCmdlet.ShouldProcess($state.LogFile, $queryShrink))) { Write-Verbose 'SQL TRX SHRINK: Invoke database transaction log backup' Invoke-DbaQuery @sqlConnection -Query $queryBackup | Out-Null Write-Verbose 'SQL TRX SHRINK: Invoke transaction log shrink command' Invoke-DbaQuery @sqlConnection -Query $queryShrink | Out-Null # Get and show the transaction log state $state = Get-SqlDbTrxLogState @sqlConnection Write-Output $state } if ($state.FileSize -lt $TargetSize -and ($Auto.IsPresent -or $PSCmdlet.ShouldProcess($state.Logfile, $queryResize))) { Write-Verbose 'SQL TRX SHRINK: Set database transaction log to target size' Invoke-DbaQuery @sqlConnection -Query $queryResize # Get and show the transaction log state $state = Get-SqlDbTrxLogState @sqlConnection Write-Output $state } } |