Autogrowth for data file

  • WhiteLotus

    SSChampion

    Points: 11908

    Hi All

    I am looking at the recommendation of Data file autogrowth per database . The autogrowth will be based on MB instead of % . To get the autogrowth number , I have the script to check the Average daily growth per database and will multiply the number with 7 ( as I want to see grow every 7 days ).

    The thing is some databases has zero growth or even minus due to shrinking process

    How do deal with this ?

    I mean if it has zero growth , how do I set the autogrowth number ?

    Thank you

    Your feedback is much appreciated

  • Jeff Moden

    SSC Guru

    Points: 995467

    If it has zero or negative growth, why are you worried about the precise value of an autogrowth number? Pick a number (for example, 50 or 100MB) and move on.

    I also think you may be spending a bit too much concern about autogrowth.  While I agree that setting it way to small or way too big can be a bad thing, autogrowth of MDF/NDF files isn't a performance concern if you have "Instant File Initialization" enabled.  If you're not allowed to have it enabled then, yeah, planned growth can be a winning move.

    Autogrowth of LDF files is a whole 'nuther story because "Instant File Initialization" doesn't help there so much.  Fortunately, they shouldn't need to grow often.

    Setting both to some value in MB rather that % is definitely a winning move, though.

    Shifting gears a bit, what you should really do is be very concerned about your "shrinking process".  That's normally one of the worst things in the world you can do to a perfectly good MDF/NDF file and is a total waste of time for LDFs unless you first figure out what is causing undesirable log file growth and fixing it.  Then, you only need to do a 1 time shrink.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Michael L John

    One Orange Chip

    Points: 25818

    WhiteLotus wrote:

    The thing is some databases has zero growth or even minus due to shrinking process

    Please describe the "shrinking process".  And why there is one in place.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • WhiteLotus

    SSChampion

    Points: 11908

    Thanks for the response Guys . Much appreciated

    Quoting from Jeff Moden,

    "Shifting gears a bit, what you should really do is be very concerned about your "shrinking process".  That's normally one of the worst things in the world you can do to a perfectly good MDF/NDF file and is a total waste of time for LDFs unless you first figure out what is causing undesirable log file growth and fixing it.  Then, you only need to do a 1 time shrink."

    Response : We only shrink if it is really needed - for emergency case ( Auto shrink is disable )

    "Setting both to some value in MB rather that % is definitely a winning move, though."

    Question : Do you mean setting same value (in MB)  for LDF and MDF/NDF ?

    Why ?

  • Jeff Moden

    SSC Guru

    Points: 995467

    No.  I'm NOT recommending that you set a MB value the same as the MDF/NDF for LDF although they can be set the same.  "It Depends".  On what?

    The LDF file is actually a formatted file... kind of like a hard-disk is formatted.  When an LDF file is first built or grows, if formats the file with VLFs (Virtual Log Files).  The number of VLFs that the new space will be formatted with is a bit of a science.  You have have way too many (which really slows down restores from backups and other things) or way too few (which makes logfile reuse more difficult, among other things).

    Google for "sqlskills vlf" for a bunch of excellent articles on the subject remembering that some of it has changed over time and that's why they have a bunch of edits and links to new articles in some of the older articles.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • WhiteLotus

    SSChampion

    Points: 11908

    Thanks Jeff

    Sure will look for it

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply