Everytime I see a database with both AutoGrow and AutoShrink enabled I cringe. I don’t “snicker” cause I leave any and all snickering to when someone says the words “Access Database”. See here if you’re confused.
OK so why is having both AutoGrow and AutoShrink enabled on your database such a bad thing… I mean Microsoft wouldn’t give us these options unless there was a good reason to use them right? WRONG!!
I’ll admit that as a safety net, having AutoGrow set on your database is not a bad idea. The rub here is that with proper capacity planning and Database File Management the “AutoGrow” process should never actually happen. I don’t know about you but I like to know when, where, and how my database files are growing to minimize the amount of fragmentation and potential performance hit.
Let’s talk about a world where Capacity Planning and Database File Managment doesn’t exist… a world where AutoGrow and AutoShrink are both enabled… a world where if you database needs more space you let SQL Server AutoGrow and if your database has too much space you let SQL Server AutoShrink. This is a world I don’t ever want to live in… EVER
Why is this viscious cycle such a bad idea?
- Resources – If you’re constantly growing and shrinking your database files there is a potentially significant CPU and IO hit (especially with the shrink) and you’re going to end up nickel and diming yourself to death.
- Fragmentation – If you’re constantly autogrowing and autoshrinking your database files will end up in bits and pieces all over your disk. If you’re data is spread out all over the disk think of the additional performance hit in reading that data and combine that with the fact that autoshrinking can cause huge fragmentation in your indexes and that’s quite a double whammy.
- Control – If you’re relying on AutoGrow and AutoShrink to manage your database files you’re giving away control as to when these operations will execute. These processes don’t wait or care about scheduled downtime or maintenance windows… if SQL Server determines it needs to autogrow or autoshrink it’s going to do it.
At the end of the day when it comes to your Database File Management you want to be a control freak. You don’t want to put your database on a yo-yo diet where it’s constantly growing and shrinking and ending up having a negative impact on it’s “metabolism” (Performance)