After delving into Autogrowth and the best practices surrounding it (primarily being proactive and adjust the file group sizes ahead of time), there was one looming question that I did not seem to find a thorough answer to. Most articles, including TechNet, always mention the practice of setting the Autogrowth to 10-15% as a fail safe. There seems to be minimal mention of utilizing the MB growth option as a fail safe.
Say for instance you have a current database size of 500GB with a file growth of approximately ~1GB/day, give or take a couple hundred MB. Each weekend, you adjust the necessary file groups by X amount to cover the growth – say boosting it to 540GB to cover nearly a month's worth of growth. In the event that you fail to adjust it once it reaches 540GB (simply forgot, unexpected table expansion, etc.), MSSQL kicks into action and attempts to grow the database by 15% but ends up on a continual time out because the growth amount is too large for the server to handle automatically. This causes application timeouts, and thus manual intervention is needed regardless.
I bring this up because in testing, Autogrowth by percentage seems to offer for timeouts in these test cases. However, if I set the Autogrowth to a set MB, for instance 1,000MB, it grows without any issue and does so in a timely manner.
So to summarize my question, why do articles (at least the ones that I have read) heed the practice of utilizing Autogrowth by percentage instead of Autogrowth by MB in the event proactive manual file growth has not occurred? It seems to me in testing and in theory, you'd prefer Autogrow to adjust by a set MB amount.
Am I false in this thinking, or am I just reading the wrong things? It'd be great to hear what type of administrative techniques you guys utilize here.
Thanks!
Best Answer
I think you're reading the wrong things. The general consensus among SQL Server professionals is to use a fixed-size autogrowth in megabytes due to the reasons you've mentioned. I would ignore TechNet in this case.
Here is a relevant article on the topic, though it's targeted at answering one of the findings in sp_Blitz.