Autogrowth % or specific value?

Question

Autogrowth % or specific value?

Andre-427668

SSC Enthusiast

Points: 127
More actions
March 6, 2008 at 10:35 am

#187349

We have a 115 GB db. it reached the max space and autogrowth to the value set for autogrowth.
The value was set to 100GB. In my opinion, this is too much. Now we have a mdf file of 215 GB.
1) Is there a best practice i.e. use % versus specific value?
2) What is the best way to reduce this allocated space, shrink, file, data file or use shrink database?
Thank you for your help.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 1

Best practice is to grow it specifically yourself. Track over time, add space as needed to handle the next month/quarter.

I've used 10% as autogrow, just for emergencies.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

I agree you want to avoid using auto-grow as much as possible. Keep an eye on your space, and grow the files manually when they get close.

For emergency purposes, I usually set the auto-grow to a specific, smaller amount (say - 256 or 512MB, in even increments of my stripes). you can't "respect" stripe sizes with a %, and they tend to get out of hand, so I avoid them.

In 2000 (and the default config in 2005) - the space has to be zeroed out before it can be used, so 10% of a 215GB file could lock up that file for a long while.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Andre-427668 SSC Enthusiast Points: 127 More actions · Answer 3

Thank you all for your input.

The data size is about 115 GB so for us keeping and addtional 20 GB is sufficient. I'd like to recover the extra 80 gb. Now to regain the space, can I simply do a shrink data file?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Yes - with a big "asterisk". Shrinking database files tends to fragment the internals quite a bit, so once you shrink you will also want to run a reorg, and start having it rebuild indexes, etc....

If you think the DB will end up growing that big - perhaps leave it alone?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 5

You want to avoid growth in % for other reasons as well. When the db is small, say 50 MB, a 10% growth is minor. AS our newbie has discovered when the db becomes large even a 10% growth can be significant. Another factor to consider when growing databases is virtual logs. Please see http://doc.ddart.net/mssql/sql70/8_des_02_9.htm. Also look at dbcc sqlperf( logspace ).

On another note, you can use the fn_virtualfilestats function to create a report to show daily growth and percent free in order to manually grow your database on YOUR shedule.

FInally, I concur with Matt. Something caused your db to reach the end of it's file space so it is growing. Leave some extra the space so you have contigous blocks of space for additional growth rather then reducing your size, fragmenting not ony the db but the disk as well, only for it to grow again in the future.

DAB

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 6

Most people use the default settings for auto-grow and DB size... that's 10% and 1 MByte. Now, lemme tell you why it's REALLY bad to do that... first, what every one else has said about percentage growth is spot on. Those reasons would normally be enough... but no one has told you that, if you do the math, you'll find that the first Gig of space will contain 73 fragments. Fragementation is kinda tough on performance. And, it won't just be database fragementation... it'll also be physical disk fragmentation unless you have a very very smart disk drive.

And, read what some of the folks have said about planning the size of your database... that's the real way to go. Unexpected growth will freeze the server until growth is completed.

--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)

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 7

Scalability Doug

SSCertifiable

Points: 5351

March 6, 2008 at 4:26 pm

#786409

Preach on brother! Amen and halleluah!

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 8

doug@scalabilityexperts (3/6/2008)
Preach on brother! Amen and halleluah!

Heh... dang, Doug! You fit right in with me an all the other crazies 🙂 ... and it didn't even take you 400 posts! 😛 I had to laugh out loud when I saw this one! 😀

--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)

Andre-427668 SSC Enthusiast Points: 127 More actions · Answer 9

Thanks to all for helping me with this. I am good to go.