Database Auto Grow setting.

Question

Post reply

Database Auto Grow setting.

Zahid Hasan

SSC Veteran

Points: 278
More actions
April 7, 2010 at 12:26 pm

#103921

Hello There,
I would like is there standard when it comes to auto grow setting for databases on sql 2005 or sql 2008.
By default the data file is set to 10% growth. Should it be below or above?
Thanks
Hasan

Viewing 8 posts - 1 through 7 (of 7 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

It should be based on the expected growth of the database, and on minimizing file fragmentation.

Leaving it at the default 10% is a good way to end up with all kinds of problems in the future.

Unless I expect a database to be very small or very large, I usually start it at 1 Gig, and set autogrowth at 1 Gig, as a default. Then I track it and find out what the numbers should really be, and change to those.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

GSquared (4/7/2010)
It should be based on the expected growth of the database, and on minimizing file fragmentation.
Leaving it at the default 10% is a good way to end up with all kinds of problems in the future.
Unless I expect a database to be very small or very large, I usually start it at 1 Gig, and set autogrowth at 1 Gig, as a default. Then I track it and find out what the numbers should really be, and change to those.

And don't forget - this setting is really only a safety net for those rare occasions when you are not able to schedule a manual grow of the data files.

You should be monitoring the data space usage and keeping enough space available for normal day to day growth. I usually try to keep at least 20% or 6 months space available - whichever is less. When I fall below those numbers, I schedule a time when I can manually grow the file. At that time, I verify the available space on disk and start planning on when to buy (or extend) more.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Zahid Hasan SSC Veteran Points: 278 More actions · Answer 3

Thanks for the information. The databases we have on sql 2005 server doesn't grow too fast. i haven't figure out the growth rate yet. What i was concerning is that for couple of servers there is not enough free space. Leaving the data file to 10% auto grow seemed to cause the disk run of out space pretty fast. what if we change it to 1% or 2%. Will that be a problem?

vidya_pande SSCertifiable Points: 5275 More actions · Answer 4

file growth event is the costly event and can cause performance issues. Make sure file growth is not triggered very frequently and keep the adequate growth parameter.

Zahid Hasan SSC Veteran Points: 278 More actions · Answer 5

Hi Vidya, how do i make data file growth is not trigger often? how do you control that? Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Zahid Hasan (4/8/2010)
Hi Vidya, how do i make data file growth is not trigger often? how do you control that? Thanks

You do that by knowing your data. First you size your database to be able to last you 2-3 years without having to grow the file.

Then, you set the autogrowth as a precaution to an appropriate size (same setting is not appropriate for everybody). I would definitely stay away from growing it by the default of 10% though.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

george sibbald SSC Guru Points: 104210 More actions · Answer 7

Zahid Hasan (4/8/2010)
Thanks for the information. The databases we have on sql 2005 server doesn't grow too fast. i haven't figure out the growth rate yet. What i was concerning is that for couple of servers there is not enough free space. Leaving the data file to 10% auto grow seemed to cause the disk run of out space pretty fast. what if we change it to 1% or 2%. Will that be a problem?

Best to set database growth to a fixed MB size rather than a percentage, as a percentage growth will slowly (and sometimes quickly) increase as the database grows.

---------------------------------------------------------------------