SQL Server with Mr. Denny

Aug 10 2009   11:08AM GMT

Auto Grow is the bane of my existence

Denny Cherry Denny Cherry Profile: Denny Cherry


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.


2  Comments on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • Michaelm2100
    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.
    0 pointsBadges:
  • Jrmep
    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
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: