I had sworn to myself that if I saw one more helpful article about how to shrink your SQL Server database files with DBCC SHRINKFILE or how to back up your log with TRUNCATE_ONLY, I was going to write a rant about it.
SQL Server Magazine just tweeted about their latest article, a reader-submitted solution on how to shrink your database files with ease.
To make matters worse, this particular article is by a Microsoft DBA and doesn’t include a single word about the problems involved with shrinking your database files.
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.
I’m not going to reinvent the wheel by telling you why. Instead, I’m going to point to half a dozen posts explaining why this advice is just a flat out epic fail:
- Paul Randal – “Why You Should Not Shrink Your Data Files”
- Mike Walsh – “Don’t Touch That Shrink Button”
- Mike Walsh’s followup – “Shrinking is a Popular Topic”
- Kimberly L. Tripp – “8 Steps to Better Transaction Log Throughput”
- Gail Shaw – “Shrinking Databases”
- Tom LaRock – “When to Use AutoShrink”
I feel bad going nuclear on this article, but I’m not just venting about the author. This kind of advice shouldn’t clear any kind of SQL Server editorial team either.
Coming next month: “How to Reduce Your Backup Times with the Truncate Table Command!”
Learn More About Why Your SQL Server is Slow
sp_Blitz®: Free SQL Server Health Check – You’ve inherited a SQL Server from somebody, and you have no idea why it’s slow. sp_Blitz® gives you a prioritized list of health and performance issues, plus gives you URLs for more details about each issue.
Our Free 6-Month DBA Training Plan – Every Wednesday, you get an email with our favorite free SQL Server training resources. We start at backups and work our way up to performance tuning.
SQL Critical Care® – Don’t have time to learn the hard way? We’re here to help with our quick, easy 3-4 day process that gets to the root cause of your database health and performance pains. Contact us for a free 30-minute sales consultation.