Auto Grow is the bane of my existence

Published On: 2009-08-10By:

OK, that’s not entirely true, but it’s pretty damn close.  The auto grow feature of SQL Server shouldn’t be counted on.  It should be disabled, or at least used on as an emergency basis only.

Having the auto grow setting enabled will cause your database to grow when ever it needs to, not when you want it to.  It will also cause fragmentation on the disk, as the physical database files will end up becoming fragmented as your various database all grow as they need fragmenting the files across the disks.

Look at your databases, and figure out how much they are growing.  You’ll want to preallocate the space to the database so that the database space is allocated all in a single chunk on the disk.  This will allow the disk to more easily load data from the disk into the buffer cache as all the data from a single database will be contiguous on the disks.  If the databases and fragmented because of auto grow then as you are trying to load data from the disk, the disk will need to keep moving from place to place sporadically reading data from the disk which takes more time; and more time is bad.

Denny

Contact the Author | Contact DCAC

2 responses to “Auto Grow is the bane of my existence”

  1. Michaelm2100 says:

    Thank you for sharing your experience with the Auto Grow feature, Denny. In lieu of proper storage capacity planning, invoking the Auto Grow feature definitely provides a lazy-dog approach of “let SQL Server manage disk space allocation,” with the associated results you experienced. The concept is commendable, and if a future version the Auto Grow feature included an integrated disk-allocation optimization mechanism, then it could possibly reduce or eliminate the undesirable results you encountered. For now, I’ll definitely heed your recommendations.

    Thank you for your insight.

    Michael J Meichtry/Torrance, CA.

  2. Jrmep says:

    Hi Mr. Denny,

    I would like to say, I’m with you, but need to know the history grow for your database, I couldn’t say to my customer disable auto grow and not have the history of. I put auto grow if I don’t have the history, but I put a job to monitoring the next extend of my database each 15-30minutes to validate if I have space to do this extend.

    Manuel Pineda
    SQL DBA – MCTS

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via