Autgrouth value for user and tempDB Databases

  • Hi,

    What is the best pracise in mainaining autgrouth value for user and tempDB Databases.

    For large Db and as well as small DB's........

    Thanks In Advance.

  • Take a look at the following link:

    http://msdn.microsoft.com/en-us/library/ms175527.aspx

    :exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

  • Thank you for the link. Like User DB's waht is the best practise in chosing this autogrouth value . Ex: Fo 60 Gb Db with one .mdf, .ndf, 4 .ndf's......

  • I'm sure you can Google that!

    Hopefully you'll realise it's on a case-by-case basis with many different factors contributing to your strategy on how / when to grow a database.

    Here's another link.

    http://www.simple-talk.com/sql/database-administration/managing-data-growth-in-sql-server/

    :exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

  • And be very aware that growth by percentage is almost never OK. If your db is small and growing, it can fragment the hell out of the database, the log, and the underlying physical files. If it's too big, it can cause growth-stalls and log files that are difficult to "recycle".

    I don't have the link just now that explains it all but a growth of about 512 MB for log files seems to produce a good balance for the VLFs. Growth for data files probably shouldn't be less than 100MB for databases nor more than a gig or 2. I'll typically use 100MB for databases that aren't growing by leaps and bounds and 500MB for those that are (along with reserving a good amount of space for anticipated growth because growth really shouldn't take you by surprise).

    --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.

    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)

  • That's really interesting Jeff. Years ago I went on a MCP course for SQL 2000. The instructor recommended 50% (!!!!) growth as a blanket strategy.

    His reasoning was that few and large increases were better than smaller and frequent increases.

    :exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

  • This is probably the link Jeff is thinking of.

    Check the URLs it links to as well.

    With logs the important thing to be aware of the size and number of VLFS each chunk of growth will create.

    ---------------------------------------------------------------------

  • Robin Sasson (4/23/2012)


    That's really interesting Jeff. Years ago I went on a MCP course for SQL 2000. The instructor recommended 50% (!!!!) growth as a blanket strategy.

    His reasoning was that few and large increases were better than smaller and frequent increases.

    While what that instructor said about "few and large" compared to "smaller and frequent" is mostly true, there are points of diminishing returns. Using % for any type of growth except for a very narrow window in the size of a DB over it's life time usually isn't the way to go.

    --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.

    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)

  • george sibbald (4/23/2012)


    This is probably the link Jeff is thinking of.

    Check the URLs it links to as well.

    With logs the important thing to be aware of the size and number of VLFS each chunk of growth will create.

    Thanks for saving me the time. That's EXACTLY the link I was looking for.

    --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.

    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)

  • Thank you all....

Viewing 10 posts - 1 through 9 (of 9 total)

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