Unlike your lawn, which benefits from a regular mowing to stay healthy and looking nice, a database isn’t meant to be regularly shrunk just to keep its size under control.
The grass grows to a certain point, and we mow it to keep it looking nice so nobody complains. All the while we expect that in a few days we’ll be back out there mowing it again. This is normal practice to keep up a nice lawn.
It’s not normal to use DBCC SHRINKDATABASE regularly to shrink your databases with the expectation you’ll be shrinking them again in the near future.
Problems With Database Shrinking
Shrinking a database can help you regain some drive space, but there are many cons that go along with it. More index fragmentation can be expected once the shrink is complete, which can impact query performance. While the shrink process is running, it’s going to have an impact on system resources and may cause blocking issues.
Finally, if you’re shrinking a database regularly just to get back a few GB, the free space that you get back probably isn’t going to be free for long. If you’re regularly shrinking, that means it’s regularly growing.
But What About AUTO_SHRINK?
AUTO_SHRINK is a feature in SQL Server that will periodically run a database shrink process in an attempt to reclaim unused space. AUTO_SHRINK may sound beneficial. Why else would it be included as a feature in SQL Server?
The same downsides exist, though. When it runs every few minutes to check for space to shrink, it’s going to mean a performance hit on your server and negatively impact query performance due to fragmentation. It’s not worth it.
Give Me Some Space
Perhaps you purged a significant amount of data, and you don’t want that empty space sitting out there forever. In that case, shrinking a database is warranted.
But stop shrinking your databases so that they will fit onto a drive. If you’re constantly shrinking to stay right under that size threshold so you don’t get low disk space alerts, then it’s time to bite the bullet and add disk space. Give your database room to breathe.
Thanks for reading!
Chad Callihan 