November 11, 2010 at 12:06 am
Hi,
can you help me for how to set data file or log file autogrowth.
actually till now we never faced problem of autogrowth most of database are set to 10% autogrowth.
one day we got an error about autogrowth it's going to grow 80gb database autogrowth and that time it was not able to allocate and advice to minimize autogrowth.
i read many articles some suggest it should be not in percentage or in fixed size and some suggest that if it's in minimal size for example
for 8gb if i set 40 MB that can reduce performance.
for instant solution i am thinking that to increase inital file size of MDF and ldf
for example if current size is 1GB and my growth in a week 1gb then increase file size to 2GB
means in practice will observe grow of filesize of mdf and ldf and then will adjust file size accordingly that
is this a right way ???? because discussion with our db group some member told me that in autogrow it allocates betterly space near by then when we manually adjust the filesize.
please advice me for this.
thanks in advance.
Raj Acharya
November 11, 2010 at 12:15 am
raj acharya (11/11/2010)
Hi,can you help me for how to set data file or log file autogrowth.
actually till now we never faced problem of autogrowth most of database are set to 10% autogrowth.
one day we got an error about autogrowth it's going to grow 80gb database autogrowth and that time it was not able to allocate and advice to minimize autogrowth.
i read many articles some suggest it should be not in percentage or in fixed size and some suggest that if it's in minimal size for example
for 8gb if i set 40 MB that can reduce performance.
for instant solution i am thinking that to increase inital file size of MDF and ldf
for example if current size is 1GB and my growth in a week 1gb then increase file size to 2GB
means in practice will observe grow of filesize of mdf and ldf and then will adjust file size accordingly that
is this a right way ???? because discussion with our db group some member told me that in autogrow it allocates betterly space near by then when we manually adjust the filesize.
please advice me for this.
thanks in advance.
It should not be a percentage because in that case, you don't have control over the acutal size of growth, so the hit it causes you will grow on a per case basis.
Rule no 1 for any RDBMS is : "Tell the system what you know", so take control and create your files at a size you know you're going to need !! (unless that is way far in the future)
If you have "instant file initialization" enabled, this may actually be a totally other discussion, but it cannot allocate a volume that doesn't fit in your drive size.
My advize ? Take control yourself and setup alerts if autoextend occurs, so you know something isn't as predicted and the dev team must be contacted to see what's going on and if it may be bug related.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 11, 2010 at 12:42 am
Also i have recommended to read the Kimberly's(KT) link
Instant Initialization - What, Why and How?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 11, 2010 at 1:55 am
my main question is that if i will increase initial file size as per needed is this right practice ...?
for example my current file size 20gb it increase around 2 gb in week
so will increase file size either weekly from 20gb to 22gb but some from our group tell me that increasing file size manually will cause fragmentation !!! is this right so what autogrowth gone a do same thing or it will take care for this thing.
i just want to increase my filesize weekly or monthly to avoid it will go for autogrowth then also will keep that option on.
Raj Acharya
November 11, 2010 at 2:04 am
If you know that in a year's time your database is going to have grown by 100GB, then you may as well set the size to 120GB now. Keep autogrow enabled in case of unexpected growth, but monitor your data size on a daily basis.
John
November 11, 2010 at 6:58 am
is that fine if i will change filesize of datafile or logfile on weekly base means any problem that can cause performance or fragmentation issue.
Raj Acharya
November 11, 2010 at 9:09 am
If you can, just do it right now to the full size. This will not take more backup space !, but you'll neede the space if you restore.
If you didn't enable instant file initialisation, any extention will take time, and with those sizes, chances are it will be noticable.
Also, if you extend to the full size right now, chances are you'll end up with less OS-level file fragmentation.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 11, 2010 at 10:05 am
My advice is to set the space you need for the next 3 months. You don't want to manage space too frequently, and you don't want too much OS level fragmentation. If you expect 24GB in the next 3months, just set the database file to that now. I presume you are not looking to add disks every week, so just set the space, monitor regularly, and when it gets low, say 4-6GB, add another 24 (or more).
Keep in mind that you need space inside the file for maintenance ops, like index rebuilds that is equal to twice your largest table.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply