commands

^

Invoke-DbaDbShrink

Author Chrissy LeMaire (@cl), netnerds.net
Availability Windows, Linux, macOS

 

Want to see the source code for this command? Check out Invoke-DbaDbShrink on GitHub.
Want to see the Bill Of Health for this command? Check out 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.

Alias
Required False
Pipeline true (ByValue)
Default Value
-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..

Alias
Required False
Pipeline false
Default Value
-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.

Alias
Required False
Pipeline false
Default Value
-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.

Alias
Required False
Pipeline false
Default Value
-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.

Alias
Required False
Pipeline false
Default Value False
-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.

Alias
Required False
Pipeline false
Default Value 0
-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.

Alias
Required False
Pipeline false
Default Value Default
Accepted Values Default,EmptyFile,NoTruncate,TruncateOnly
-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.

Alias
Required False
Pipeline false
Default Value All
Accepted Values All,Data,Log
-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.

Alias
Required False
Pipeline false
Default Value 0
-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.

Alias
Required False
Pipeline false
Default Value 0
-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.

Alias
Required False
Pipeline false
Default Value False
-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.

Alias
Required False
Pipeline false
Default Value False
-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.

Alias
Required False
Pipeline false
Default Value False
-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.

Alias
Required False
Pipeline true (ByValue)
Default Value
-WhatIf

Shows what would happen if the command were to run.

Alias wi
Required False
Pipeline false
Default Value
-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"):

Alias cf
Required False
Pipeline false
Default Value