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.
Epic Advice Fail
SQL Server Magazine just tweeted about their latest article, a reader-submitted solution on how to shrink your database files with ease.
AAAAAAAAAAARGH.
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.
AAAAAAAAAAARGH.
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!”
I routinely have to shrink my database in order to sign-in to a specific program [i.e. Oracle Primavera P6]. So, I was scouring the internet to find ways to automate this process when I ran across your article. If I don’t shrink my database every two to three days, I literally can’t sign in to the program. Now, I’m very confused.
Carter – what’s the problem that happens when you can’t sign in? I’m guessing you’ve got the database in full recovery mode and you’re not doing transaction log backups, so the log file fills up and the database stops. In that case, you’d either want to start doing transaction log backups or put the database in simple mode.