dbatools is a free PowerShell module with over 200 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Invoke-DbaDatabaseShrink

Shrinks all files in a database. Databases should be shrunk only when completely necessary.

Shrinking is controversial

It can be tempting to shrink databases, especially for people who are newer to SQL Server and don’t yet understand t-log growth. However, 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:

But sometimes you have to

However, there are some cases where a database will need to be shrunk, such as:

  • After a big archiving job
  • After dropping large amount of tables
  • After substantial changes in a table’s data types

And when you do

In the event that you must shrink your database:

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

Screenshots

Examples

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

Invoke-DbaDatabaseShrink -SqlInstance sql2016 -Databases Northwind,pubs,Adventureworks2014

To shrink 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.

Invoke-DbaDatabaseShrink -SqlInstance sql2014 -Databases Adventureworks2014 -PercentFreeSpace 50

To shrink all databases on SQL2012 (not ideal for production)

Invoke-DbaDatabaseShrink -SqlInstance sql2012 -AllUserDatabase

Get-Help

From PowerShell, execute Get-Help Invoke-DbaDatabaseShrink -Detailed for more information on this function.

Source Code

Want to see the source code? View Invoke-DbaDatabaseShrink.ps1 on GitHub
 

Related commands