David Postlethwaite who contributes to this blog regularly sent me an email this morning asking about Database Auto grow.

This was his question

Do you set the database and logs to grow on demand.
Advantage:       Only using expensive shared disk space as required
Disadvantage:   Causes slow database response when the files have to expand. If auto growth size  is set too low then too many small virtual logs are created leading to poor performance
                     
If you set it what sort of growth sizes do you use (%tage or fixed size)
How many people have changed the auto growth settings from the default of 1MB and 10%?
 
 
Do you manually size the database and logs and disk for expected growth for the year and then turn off auto growth
Advantage: No slow response caused by auto growth during busy periods
Disadvantage: Lots of expensive shared disk is used up with empty space

I replied with the following:

“I set the file sizes to decent amount to allow for say 12-18 months growth and monitor it regularly.

I prefer to change file sizes manually as this will allow you to control when the files grow. File growth can have an overhead.

I prefer to leave auto grow turned on as a fail back.You won't need or want to get called out about it overnight on the weekend etc. If you turn it off the files need to grow the DB fall over until it can grow.

I prefer a fixed size in MB for the growth a % can mean it grows by different amounts. 10% of 100mb is different from 10% of 100GB etc.

Re the log growth: You need to ensure the log is sized appropriately and if auto grow is set, it grows by a reasonable size. You will need to manage the VLFs (Virtual Log Files) - this is interesting and Kimberly l Tripp over at SQL Skills  has some great stuff on  VLFs on her blog.

I have an old post on database auto growth that also argues these points

So then I’d thought this might be an interesting talking point. I have set up a poll which you can see on the right-hand side of the blog. If you are reading through a RSS reader you will need to visit www.gethynellis.com  to take part.

I think David will be interested in your responses, so please take a minute to leave your thoughts