Add space to a database a little at a time or large chunk?

Question

Add space to a database a little at a time or large chunk?

jason.stephens

Ten Centuries

Points: 1088
More actions
September 17, 2008 at 10:54 am

#123574

From what I know, little chunks could have issues with the indexes and cause slow downs if set to autogrow during peak times, but if you rebuild the indexes regularly, are there any other adverse effects?
Would you recomment a manual large growth of the database or small autogrow?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

DNA_DBA SSCrazy Eights Points: 9132 More actions · Answer 1

I'd try to size the database appropriately to start off with and avoid autogrow. Use autogrow as a way of allowing the database to grow in an emergency rather than as a normal operation and then use a reasonable size chunk rather than a smaller one where it is likely to need to grow again and again.

tosscrosby SSCoach Points: 18045 More actions · Answer 2

DNA is absolutely spot on. One reason to avoid the smaller autogrows is disk level fragmentation of your database. Using disk dfrag will help but the database should be offline to perform this task safely and correctly. Disk level fragmentation will defintely lead to performance issues at some point.

-- You can't be late until you show up.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

I concur... basically, auto-growth should never take you by surprise... it should be a planned event and it should be made based on a plan derived from usage patterns. Auto-growth, when it occurs, is pretty much a show stopper while it occurs and small growths can frag the underlying hard disk pretty badly depending on growth settings. Of course, because of it's nature, auto-growth will always happen when you can least afford it... like month-end reporting, etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)