Just like the debate over whether the chicken or the egg came first, the debate over whether you should or shouldn't shrink databases is still going strong. This debate took center stage when the Reader to Reader article "Utilities Assess Data-File Usage and Provide Commands So You Can Do Something About It" was published. In this article, David Paul Giroux discussed how to use two T-SQL utilities (Candidate Commands and Candidate Commands Plus) to manage the size of data files. These utilities provide not only information about disk and data-file usage but also commands you can use to shrink or grow the size of data files.
In response to "Utilities Assess Data-File Usage and Provide Commands So You Can Do Something About It", readers shared their views on the practice of shrinking databases in the article's reader comment area, on SQL Server Magazine's Twitter page, and in their own blogs. We want to share some of those comments with you in hope of getting a discussion going on whether shrinking databases is acceptable, and if so, when it's okay to do so. If you'd like to share your thoughts, click the View Reader Comments link at the top of the page, scroll up, and enter your comments in the text box.
Really Dangerous Advice
Reader jsegarra was the first to comment on the article. Believing that shrinking databases isn't a good idea, he commented:
"I'm sorry but you really shouldn't be shrinking databases in the first place. No offense."
Brent Ozar (brento) echoed that sentiment when he wrote,
Note that the item Ozar is referencing is his blog, "Stop Shrinking Your Database Files. Seriously. Now." In that blog, he writes,
"Don't shrink your database files just to free up drive space. Stop. It's an unbelievably, disgustingly, repulsively bad idea. Your disk drive space is for files, not for ornamentation. You don't get bonused based on the amount of free space on your drives. Empty files don't take longer to back up. And so help me, if you find yourself shrinking databases so often that you have to automate it, you need to cut up your DBA card and reconsider your choice of career."
Ozar's blog also includes links to other posts about the topic of shrinking databases, including Paul Randal's blog post "Why you should not shrink your data files".
The Script is Just a Tool
When readers' comments started streaming in, we asked Giroux for his thoughts. He wrote:
"The script is a tool. The script does not make any changes. The article is narrow in scope and gives NO ADVICE. The article is not in regards to file management and does not attempt to be all encompassing. There are countless articles regarding file management, I don't desire to write another one. No DBA should attempt to read one single article on one single issue and expect his/her training to be complete. Learn file management elsewhere. As Paul Randal states in his post 'Why you should not shrink your data files': 'Bottom line - TRY TO AVOID running data file shrink at all costs!' (I added the emphasis on 'try to avoid'.) If the competent DBA decides a file needs to be shrunk OR GROWN, this script can assist the DBA."
"I created this script because of one particular server that had 24 databases and no free disk space. One of the databases ran out of database free space. I could not unilaterally truncate tables, or move databases to a different server or add physical drives. The only thing I could do instantly was rob Peter to pay Paul. Yes. I shrunk the file of Database A so I could increase the file for Database B. That gave me time to THEN come up with a long term solution."