· Uwe Janke
Commands / Invoke-sqmLogShrink

Invoke-sqmLogShrink

MaintenancesqmSQLTool v1.8.2+ · VLF analysis since v1.9.178.0✓ -WhatIf supported
Shrinks transaction log files (LDF) using DBCC SHRINKFILE. Target size is calculated as a percentage of current size (ShrinkTargetPercent, default 10%) with a minimum threshold (MinTargetMB, default 64 MB). For Always On AG members, automatically redirects to the primary replica. System databases and offline databases are skipped.

Before and after the shrink every database is analysed (metadata only, no scans), so you learn why the log does not get smaller: Use -AnalyzeOnly to get the analysis without shrinking anything.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMETarget SQL Server instance. Auto-redirects to AG primary if applicable.
-SqlCredentialPSCredentialOptional, Credentials for the connection.
-Databasestring[]OptionalAll user DBsTarget databases (wildcards allowed). System and tempdb always skipped.
-AllswitchOptional$falseExplicitly process all user databases.
-ShrinkTargetPercentintOptional10Target as % of current log size (1-99).
-MinTargetMBintOptional64Minimum target size in MB (floor limit).
-AnalyzeOnlyswitchSwitch$falseOnly analyse (wait reason, active VLFs, VLF count and cause), do not shrink. Status Analyzed.
-ContinueOnErrorswitchSwitch$falseContinue to next database on error.
-EnableExceptionswitchSwitch$falseRe-throw exceptions immediately.
-WhatIf / -ConfirmswitchOptional, ShouldProcess (ConfirmImpact: None) guards shrink execution. The analysis still runs.

Return Value

ColumnMeaning
StatusShrunk, NoChangeNeeded, Blocked (active part at the end of the file), Analyzed, WhatIfSkipped, Skipped, Failed
SizeBefore_MB / TargetSize_MB / SizeAfter_MB / SpaceSaved_MBLog file sizes
LogReuseWait, LogUsed_Pctlog_reuse_wait_desc and log usage (after the shrink, if it ran)
MinReachable_MBEnd of the last active VLF: the smallest size the shrink can reach right now
Cause, RecommendationWhy the log is active and what to do (log backup, end the transaction, check the secondary, CHECKPOINT, ...)
VlfCount, VlfCountAfter, ActiveVlfCount, VlfStatusVLF count before/after, active VLFs, OK / Warning (300+) / Critical (1000+)
VlfCause, VlfRecommendationWhy there are many VLFs and how to fix it (shrink, regrow in one step, fixed autogrowth)
DetailsOldest open transaction, last log backup, replica queues

Execution Flow

START AG check (redirect to primary), Get-DbaDatabase Apply -Database wildcard filter | skip system + offline DBs foreach database / log file Analysis (metadata only) log_reuse_wait_desc + detail: last log backup, oldest open transaction, AG queues sys.dm_db_log_info: VLF count, active VLFs, end of the last active VLF VLF cause: percent growth, small growth, VLFs below 1 MB; log WARNING with advice -AnalyzeOnly? YES Status Analyzed, next NO Active VLF at end of file? YES Status Blocked cause + advice, no shrink NO ShouldProcess: DBCC SHRINKFILE (log, TargetSize) TargetSize = max(CurrentSize * ShrinkTargetPercent / 100, MinTargetMB) WhatIf: Status WhatIfSkipped | catch: Failed (continue if -ContinueOnError) Analysis again: new size, VLF count before -> after Target not reached: message names the concrete cause and recommendation Status Shrunk Return [PSCustomObject[]] per log file Sizes, Status, LogReuseWait, Cause, Recommendation, VlfCount, VlfStatus, VlfCause DONE

Examples

Why does the log not shrink, why so many VLFs? (analysis only, no shrink)
Invoke-sqmLogShrink -SqlInstance "SQL01" -AnalyzeOnly |
    Format-List Database, LogReuseWait, Cause, Recommendation, VlfCount, VlfStatus, VlfCause, VlfRecommendation
Shrink log file to 20% of current size
Invoke-sqmLogShrink -Database "MyDB" -ShrinkTargetPercent 20
All databases with -WhatIf
Invoke-sqmLogShrink -SqlInstance "SQL01" -All -WhatIf
Typical output with an open transaction (lab run)
LogReuseWait   : OLDEST_PAGE
Cause          : Indirekter Checkpoint: die aelteste schmutzige Seite haelt das Log (OLDEST_PAGE).
                 Zusaetzlich offen: Aelteste Transaktion: Session 67 (app von WEB01, ShopApp, Status sleeping),
                 offen seit 2026-10-17 11:54 (0 min) ...
Recommendation : Offene Transaktion abschliessen lassen bzw. Sitzung pruefen (Get-sqmWhoIsActive,
                 notfalls Stop-sqmSqlProcess). CHECKPOINT ausfuehren und den Shrink wiederholen.