Invoke-sqmLogShrink
MaintenancesqmSQLTool v1.8.2+ · VLF analysis since v1.9.178.0✓ -WhatIf supported
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:
- Why the log is active:
log_reuse_wait_descwith details. LOG_BACKUP (time of the last log backup), ACTIVE_TRANSACTION (session, login, host, program, open since), AVAILABILITY_REPLICA (lagging or suspended secondary with send/redo queue), REPLICATION (log reader or CDC), CHECKPOINT/OLDEST_PAGE and more. The oldest open transaction is always reported, even when SQL Server shows a different wait (right after a log backup it usually says OLDEST_PAGE). - Where the last active VLF sits: DBCC SHRINKFILE can only shrink up to its end. If it sits at the end of the file, the shrink is skipped with Status
Blockedand the reason, even when the wait is NOTHING. - Why there are so many VLFs (from 300 Warning, from 1000 Critical): percent growth, small fixed growth, many VLFs below 1 MB from earlier small growths or shrink/grow cycles, with a recommendation for target size and autogrowth.
-AnalyzeOnly to get the analysis without shrinking anything.Parameters
| Parameter | Type | Required | Default | Notes |
|---|---|---|---|---|
| -SqlInstance | string | Optional | $env:COMPUTERNAME | Target SQL Server instance. Auto-redirects to AG primary if applicable. |
| -SqlCredential | PSCredential | Optional | , | Credentials for the connection. |
| -Database | string[] | Optional | All user DBs | Target databases (wildcards allowed). System and tempdb always skipped. |
| -All | switch | Optional | $false | Explicitly process all user databases. |
| -ShrinkTargetPercent | int | Optional | 10 | Target as % of current log size (1-99). |
| -MinTargetMB | int | Optional | 64 | Minimum target size in MB (floor limit). |
| -AnalyzeOnly | switch | Switch | $false | Only analyse (wait reason, active VLFs, VLF count and cause), do not shrink. Status Analyzed. |
| -ContinueOnError | switch | Switch | $false | Continue to next database on error. |
| -EnableException | switch | Switch | $false | Re-throw exceptions immediately. |
| -WhatIf / -Confirm | switch | Optional | , | ShouldProcess (ConfirmImpact: None) guards shrink execution. The analysis still runs. |
Return Value
| Column | Meaning |
|---|---|
| Status | Shrunk, NoChangeNeeded, Blocked (active part at the end of the file), Analyzed, WhatIfSkipped, Skipped, Failed |
| SizeBefore_MB / TargetSize_MB / SizeAfter_MB / SpaceSaved_MB | Log file sizes |
| LogReuseWait, LogUsed_Pct | log_reuse_wait_desc and log usage (after the shrink, if it ran) |
| MinReachable_MB | End of the last active VLF: the smallest size the shrink can reach right now |
| Cause, Recommendation | Why the log is active and what to do (log backup, end the transaction, check the secondary, CHECKPOINT, ...) |
| VlfCount, VlfCountAfter, ActiveVlfCount, VlfStatus | VLF count before/after, active VLFs, OK / Warning (300+) / Critical (1000+) |
| VlfCause, VlfRecommendation | Why there are many VLFs and how to fix it (shrink, regrow in one step, fixed autogrowth) |
| Details | Oldest open transaction, last log backup, replica queues |
Execution Flow
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.