April 7, 2010 at 12:26 pm
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
April 7, 2010 at 1:31 pm
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
April 7, 2010 at 5:21 pm
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
April 8, 2010 at 8:57 am
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?
April 8, 2010 at 9:10 am
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.
April 8, 2010 at 3:51 pm
Hi Vidya, how do i make data file growth is not trigger often? how do you control that? Thanks
April 8, 2010 at 4:17 pm
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
April 8, 2010 at 4:20 pm
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.
---------------------------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply