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
2 Responses
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.
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