July 21, 2009 at 9:37 am
Our primary DB is ~400GB, and the last time the .mdf primary data file grew, it expanded by 20%, instead of the 10% that it was set at. A month ago, I set the autogrowth to 5%. However, when it grew last week, it expanded by 10% instead of 5%.
The log is set to 10% autogrowth, but the data is set to 5%. Any idea why the last two growth sessions increased double what they were set to autogrow as?
July 21, 2009 at 9:53 am
bdragoo (7/21/2009)
Our primary DB is ~400GB, and the last time the .mdf primary data file grew, it expanded by 20%, instead of the 10% that it was set at. A month ago, I set the autogrowth to 5%. However, when it grew last week, it expanded by 10% instead of 5%.The log is set to 10% autogrowth, but the data is set to 5%. Any idea why the last two growth sessions increased double what they were set to autogrow as?
How about providing actual growth in MB instead of %. It is hard to answer your question without firm data.
July 21, 2009 at 10:00 am
there is also a bug in SQL 2005 with autogrowth when you restore a db or restart the services etc.
In the view sys.database_files is_growth_percent changes from 0-1, which means that earlier the value in mb is converted to percent, it happened to me many times, be aware of that.
Its always better to have the auto-growth value is MB instead of percent, as sys has to use many resources to calculate the value!!!
July 21, 2009 at 10:00 am
A guess:
Database size (in MB) Growth (@ 10%)
100 10
110 11
121 12.1
133.1 13.31
146.41 14.641
Does that make sense?
You really should set the the log and data files to grow a set size every time instead of a given percentage. using a percentage, each time it grows it will grow by a large amount.
July 21, 2009 at 10:02 am
dba_pkashyap (7/21/2009)
there is also a bug in SQL 2005 with autogrowth when you restore a db or restart the services etc.In the view sys.database_files is_growth_percent changes from 0-1, which means that earlier the value in mb is converted to percent, it happened to me many times, be aware of that.
Its always better to have the auto-growth value is MB instead of percent, as sys has to use many resources to calculate the value!!!
Is this bug documented somewhere? I have never had an issue with it and I am curious.
July 21, 2009 at 10:14 am
I havent seen it documented anywhere, but I had it in my experience many times.
Autogrowth value going to 6-7digit as percentage!!!
July 21, 2009 at 1:39 pm
This issue was first reported here: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
And, fixed here: http://support.microsoft.com/kb/919611/
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply