Post reply

Autogrowth and initial size

  • November 21, 2011 at 5:11 am

    #247169

    Can someone please the "Autogrowth and initial size" under the database properties/files menu.

    It says "By 10 percent, unrestricted growth"

    I am very new to SQL. This makes no since to me. I believe its talking about how fast to allow the database

    or transaction log to grow and how much it could grow.

    Please give me a detailed explanation about this.

    Thanks

  • November 21, 2011 at 5:20 am

    #1410615

    This is a safety guard.

    You should preset the correct data & log file sizes in the db for the next 6-24 months of planned db growth.

    On top of that you should have a preset growth, ideally in mb so that the operation can happen as fast as possible.

    You need to test to see how fast your drives are. Because the transaction(s) will be waiting on that to be able to complete. So while you want that figure to be as high as possible, you don't want to go insane either.

    Most people use ± 1GB as a starting point and adjust from there.

    If you don't use autogrow your transactions will fail and the app will "break".

  • November 21, 2011 at 5:26 am

    #1410621

    What happens if I preset the transaction log to 50gb and I reach that limit?

    Last month I did a "Shrink DB" command. The transaction log was up to about 11gb. 3 Weeks later, its about 115mb.

  • November 21, 2011 at 5:40 am

    #1410627

    dale.schwabjr (11/21/2011)

    What happens if I preset the transaction log to 50gb and I reach that limit?

    Last month I did a "Shrink DB" command. The transaction log was up to about 11gb. 3 Weeks later, its about 115mb.

    Once the limit is reached, either the log will grow if you allow it and have more room on the drive(s) or the operation will fail.

    That's impossible for the tlog to go from 11 GB to 115 mb without someone shrinking the file (or any other manual intervention).

    Once the file reaches a certain size it's because it NEEDS to be that size. Shrinking it will only cause it to grow back to that size, slow the server down while doing so and creating physical file fragmentations on top of too many vlfs => http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

    Shrink is ok if you had a 1 time event that you know will NEVER happen again (like a runaway transaction or massive purge job).

  • November 21, 2011 at 6:43 am

    #1410669

    I shrank the tlog manually. I thought I needed to so the performance would improve. But, it sounds like your saying

    you should not need to shrink the tlog. Is that correct?

    Please correct me if I'm wrong...the transaction log gets cleared out on a backup right?

    Thanks so much for your help

  • November 21, 2011 at 6:45 am

    #1410672

    All your questions answered here :

    http://www.sqlservercentral.com/articles/Transaction+Log/72488/

    http://www.sqlservercentral.com/articles/64582/

  • November 21, 2011 at 7:10 am

    #1410697

    Thanks for all your help.

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

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