automatically grow file

Question

automatically grow file

cross

SSCommitted

Points: 1735
More actions
January 13, 2004 at 12:04 pm

#85220

I have a database set with the option to grow automatically. It is set to grow by 10%, which is the default.
Data gets imported into it each week. I have found that it doesn't use most of the space it creates from the 10% growth. Ex. There are 4496 MB allocated but only 2367 MB are actually used.
Is there a particular reason the default growth % is 10?. Would it cause potential problems to move that down to, say, 5%?

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

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

No idea why default is 10%. By decreasing to 5%, Database performance may be impacted during database growth that involves slow I/O activities more frequently.

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 2

I seldom use a percentage for autogrow ether. When creating a database I make estimates for db initial size, and how much the db will grow over time.

Then I make the filesize big enough to accomodate initial size of db + some growth. The autogrow is set to a MB value that will increase the size in chunks when needed. Also I set up a realistic max size so that there is an upper limit as to how much data the db can contain.

If the db approaches the max size, desicions can be made as to either increase Max size or purge data.

If I use the % value, I usually set it to 50%, and -important- set an appropriate initial size that is not too small. ( And an upper limit as well )

/rockmoose

You must unlearn what You have learnt

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 3

Also related to this topic is the subject of defragging database files. Especially for a database that has been allowed to grow and shrink often, it can get extremely fragmented and significantly decrease performance. Even on a database built with lots of room and large autogrow, if the db was built on a "mature" server it may be heavily fragmented.

Using Windows Defrag or third-party tools will not defrag open databases. You need to occasionally check and take the dbs offline, and defrag the files then. At some sites, I have setup monthly maintenance jobs to detach the database, use Contig to defrag the individual files, then reattach the database (all done after a good backup, of course). Contig is a freeware file defrag from http://www.systernals.com folks.

Mark