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.
Reduces the physical size of database files by removing unused space from data and log files.
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:
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>]
PS C:\> Invoke-DbaDbShrink -SqlInstance sql2016 -Database Northwind,pubs,Adventureworks2014
Shrinks Northwind, pubs and Adventureworks2014 to have as little free space as possible.
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.
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.
PS C:\> Invoke-DbaDbShrink -SqlInstance sql2012 -AllUserDatabases
Shrinks all user databases on SQL2012 (not ideal for production)
PS C:\> Get-DbaDatabase -SqlInstance sql2012 -Database Northwind,pubs | Invoke-DbaDbShrink
Shrinks all databases coming from a pre-filtered list via Get-DbaDatabase
The target SQL Server instance or instances. Defaults to the default instance on localhost.
Alias | |
Required | False |
Pipeline | true (ByValue) |
Default Value |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Shows what would happen if the command were to run.
Alias | wi |
Required | False |
Pipeline | false |
Default Value |
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 |