Press "Enter" to skip to content

When Shrinking Makes Sense

Eitan Blumin reminds us that database shrinking isn’t always bad:

Shrinking databases and database files in SQL Server is a widely known “worst practice”.

Usually, it’s because it’s assumed that the database files are expected to auto-grow again after the shrink.
So, in truth, it’s not the shrink itself that’s the problem… It’s the auto-growth!

But… What if you DON’T expect the database file to auto-grow back to what it was before?
For example, what if you truncated/deleted/migrated/archived huge chunks of data from your database, which you don’t expect to be returned later? Or what if you performed a massive data compression or migrated to clustered columnstore indexes, which reduced your data size significantly?

Read on for more thoughts along these lines, problems you might run into, and scripts to help you along the way.