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