The Great Debate: Should You Shrink Databases?

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,

"This is really, really, REALLY dangerous advice that can cause performance problems. I responded to it in full here: http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/"

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

After reading Giroux's thoughts, Ozar commented:

"When you say 'The article is narrow in scope and gives NO ADVICE,' it seems to ignore one of the early lines in the article: 'In order to grow the data file for DB without depleting all available disk space, you must first free up some disk space by shrinking other data files.'

"You're telling the DBA that their only option - not one of, but their ONLY option - is to shrink other data files. That's bad advice, and that's what we're trying to avoid. One other option might be to find out why the other data files are large, and get the right amount of disk space to handle the problem. Otherwise, with your solution, you're recommending that they keep growing and shrinking different files, which ends up being a fragmentation nightmare. If you could have included the other options in the article, just like you explained them in the comments, then the article would have stood great on its own. Otherwise, it comes off as an instruction manual on how to build a handgun. While some publications have no problem explaining how to build a handgun, my hope is that we strive for a higher level of education at SQL Server Magazine. If we start publishing articles on how to truncate tables or how to format drives - without explaining the risks and drawbacks - then we're doing a bad job of educating the community."

Not Ashamed of Being a Shrinker

Also weighing in on the topic was Bill McEvoy. Here's what he had to say:

"Of all the things to get excited about in the MSSQL world, I never thought shrinking databases would be one of them. To me, it has always been a no-brainer. I'm running out of disk space, and I find a database with a ton of free space. What do I do? I shrink it! Somebody issues a massive update statement that grows the transaction log beyond all reason. What do I do? I shrink it! I'm a shrinker, and I'm not ashamed of it!"

"Now before you go an accuse me of being a 'no-brainer' myself, hear me out. I'm a DBA consultant. I have multiple large clients who have hundreds upon hundreds of databases. Most of these databases haven't been given the love and attention they deserve. As a result, I often inherit poorly managed systems and end up spending most of my time putting out fires."

"The single most occurring fire is low disk space. Not every organization is blessed with endless terabytes of disk storage. One of my clients has a large virtual server farm, and each virtual server gets just about enough disk space to hold the main database and a few backup files. Yes, I know, I have more space on my MP3 player, but this is the 'do more with less' world I work in. Unless the DBA team cries for more space, we're stuck with what we're given. New servers crop up faster than we can document, let alone manage. Since adding more disk space requires a reboot, we usually have to shrink the data files and transaction log in order to keep the server up and running."

"That being said, a DBA should still exercise caution prior to shrinking/truncating a live production database. Outages need to be scheduled, backups need to be confirmed, etc. As the author stated in his response, sometimes you have to do what you have to do. And his script helps me do just that."

"What it comes down to is that if you shrink a database, you really need to defragment the indexes and data pages. But you are already doing that regularly anyway, right?"

What Do You Think?

Now that you know how some of your peers feel about shrinking databases, how do you feel about it? Let us know by entering your comments in the text box below.