November 29, 2007 at 11:11 am
I have a SQL 2005 DB that its MDF file is growing at a rate of 1 GB per day, I currently have it set up to unrestricted growth by 500 MB. Should I increase that growth to 1 GB? what would the impact of this change be? what are best practices when it comes to setting up autogrowth for MDF and LDF files?
Thanks,
Carlos
November 29, 2007 at 11:16 am
for sql 2005 adding space is not taking time like sql 2000. though if you know it would increase by 1 GB every day you should manually increase the space needed for a longer period...this will improve performance also as fragmentation would be less...
the basic concept behind having automated increase is in case for some reason manual spacing was not done....
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
November 29, 2007 at 12:00 pm
What I tend to do is estimate out 3 years of growth and size the databases I support initially to be that 3-year final size. Why 3 years? That is our estimated lifespan for hardware in our environment. If you have the space dedicated for SQL, then you may as well claim it up front and avoid autogrowth impact and external fragmentation that will occur as a result of autogrowth. You can still leave autogrowth set to 1GB after resizing the database, but that would only be precautionary. The hard part is done. You know what the growth rate is! That is typically the hardest factor to determine because it is not consistent. At the bare minimum I suggest sizing your database out for another 300GB and then reset autogrowth for 50GB if you can not resize the DB for the full expected lifespan of the hardware it resides upon.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
November 29, 2007 at 6:08 pm
Good thought process while concerning the growth rate. But i would also think about the fact that at 300GB the server is relatively small in comparison to whats out there today (Terabits). So another possible idea adding on what was said but instead of growing in chunks of 50GB...I would just start the database at 300 or 400GB size and then set the growth rate to about 20 % ... this will give you a minimal of 6 to 8 GB in growth and a bit less concern about running out of disk space. Percentages give you a bit more size control in my eyes yet some like to use fixed growth rates like the 50 GB that was suggested(just depends on what you like personally) and its all dependant on the amount of disk space you have.
-D-
DHeath
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply