Invoke-DbaDbShrink

Synopsis

Reduces the physical size of database files by removing unused space from data and log files.

• Shrinks can cause severe index fragmentation (to the tune of 99%)
• Shrinks can cause massive growth in the database’s transaction log
• Shrinks can require a lot of time and system resources to perform data movement

Description

Reduces database file sizes by removing unused space from data files, log files, or both. This function targets specific files within databases and can reclaim substantial disk space when databases have grown significantly beyond their current data requirements.

Use this function sparingly and only when disk space recovery is critical, such as after large data deletions, index rebuilds, or when preparing databases for migration. The function supports chunked shrinking operations to minimize performance impact and provides detailed fragmentation statistics to help assess the operation’s effects.

Many awesome SQL people have written about why you should not shrink your data files. Paul Randal and Kalen Delaney wrote great posts about this topic:

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files
https://www.itprotoday.com/sql-server/shrinking-data-files

However, there are some cases where a database will need to be shrunk. In the event that you must shrink your database:

1. Ensure you have plenty of space for your T-Log to grow
2. Understand that shrinks require a lot of CPU and disk resources
3. Consider running DBCC INDEXDEFRAG or ALTER INDEX … REORGANIZE after the shrink is complete.

Syntax

Invoke-DbaDbShrink
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [-SqlCredential <PSCredential>]
    [-Database <Object[]>]
    [-ExcludeDatabase <Object[]>]
    [-AllUserDatabases]
    [-PercentFreeSpace <Int32>]
    [-ShrinkMethod <String>]
    [-FileType <String>]
    [-StepSize <Int64>]
    [-StatementTimeout <Int32>]
    [-ExcludeIndexStats]
    [-ExcludeUpdateUsage]
    [-EnableException]
    [-InputObject <Database[]>]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Invoke-DbaDbShrink -SqlInstance sql2016 -Database Northwind,pubs,Adventureworks2014

Shrinks Northwind, pubs and Adventureworks2014 to have as little free space as possible.

Example: 2

PS C:\> Invoke-DbaDbShrink -SqlInstance sql2014 -Database AdventureWorks2014 -PercentFreeSpace 50

Shrinks AdventureWorks2014 to have 50% free space. So let’s say AdventureWorks2014 was 1GB and it’s using 100MB space. The database free space would be reduced to 50MB.

Example: 3

PS C:\> Invoke-DbaDbShrink -SqlInstance sql2014 -Database AdventureWorks2014 -PercentFreeSpace 50 -FileType Data -StepSize 25MB

Shrinks AdventureWorks2014 to have 50% free space, runs shrinks in 25MB chunks for improved performance.

Example: 4

PS C:\> Invoke-DbaDbShrink -SqlInstance sql2012 -AllUserDatabases

Shrinks all user databases on SQL2012 (not ideal for production)

Example: 5

PS C:\> Get-DbaDatabase -SqlInstance sql2012 -Database Northwind,pubs | Invoke-DbaDbShrink

Shrinks all databases coming from a pre-filtered list via Get-DbaDatabase

Optional Parameters

-SqlInstance

The target SQL Server instance or instances. Defaults to the default instance on localhost.

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance..

-Database

Specifies which databases to shrink on the target instance. Accepts wildcard patterns and multiple database names.
Use this when you need to shrink specific databases rather than all databases on the instance.

-ExcludeDatabase

Excludes specific databases from the shrink operation when processing multiple databases. Accepts wildcard patterns.
Useful when shrinking all user databases but want to skip critical production databases or those with specific maintenance windows.

-AllUserDatabases

Targets all user databases on the instance, excluding system databases (master, model, msdb, tempdb).
Use this for maintenance operations across an entire instance while preserving system database integrity.

-PercentFreeSpace

Sets the percentage of free space to maintain in the database files after shrinking, ranging from 0-99. Defaults to 0.
Leave some free space (10-20%) to accommodate normal database growth and reduce the need for frequent auto-growth events.

-ShrinkMethod

Controls how SQL Server performs the shrink operation. Default moves data pages and truncates files.
EmptyFile migrates all data to other files in the filegroup. NoTruncate moves pages but doesn’t truncate. TruncateOnly reclaims space without moving data.
Use TruncateOnly when possible as it’s the least resource-intensive and doesn’t cause data movement or fragmentation.

-FileType

Determines which database files to target for shrinking: All (data and log files), Data (only data files), or Log (only log files). Defaults to All.
Use Data when you only need to reclaim space from data files after large deletions. Use Log to specifically target transaction log files after maintenance operations.

-StepSize

Breaks large shrink operations into smaller chunks of the specified size. Use PowerShell size notation like 100MB or 1GB.
Chunked shrinks reduce resource contention and allow for better progress monitoring during large shrink operations. Recommended for databases being shrunk by several gigabytes.

-StatementTimeout

Sets the command timeout in minutes for the shrink operation. Defaults to 0 (infinite timeout).
Large database shrinks can take hours to complete, so the default allows operations to run without timing out.

-ExcludeIndexStats

Skips collecting index fragmentation statistics before and after the shrink operation.
Use this to speed up the shrink process when you don’t need fragmentation analysis or are planning to rebuild indexes immediately afterward.

-ExcludeUpdateUsage

Skips running DBCC UPDATEUSAGE before the shrink operation to ensure accurate space usage statistics.
Use this to reduce operation time when space usage statistics are already current or when immediate shrinking is more important than precision.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.

-InputObject

Accepts database objects from the pipeline, typically from Get-DbaDatabase output.
Use this for advanced filtering scenarios or when combining multiple database operations in a pipeline.

-WhatIf

Shows what would happen if the command were to run.

-Confirm

Prompts for confirmation of every step. For example:
Are you sure you want to perform this action?
Performing the operation “Shrink database” on target “pubs on SQL2016\VNEXT”.
[Y] Yes [A] Yes to All [N] No [L] No to All [S] Suspend [?] Help (default is “Y”):