Post reply

Are my DB Settings ok?

  • December 14, 2011 at 12:56 am

    #261597

    I have database size of 305 GB and its MDf Autogrowth setting is: By 1MB,Unrestricted Growth

    its LDf Autogrowth setting is: By 10%,Restricted Growth to 2097152

    Mdf size is:300 GB

    Ldf size is:13 gb

    In one day the differential backup difference is around 0.42 gb.

    Are my DB autogrowth Settings ok?What precautions should I take during changes?

    Thanks

  • December 14, 2011 at 1:08 am

    #1421068

    IMO for 300 GB database autogrowth should be 1GB or more.

  • December 14, 2011 at 1:22 am

    #1421074

    Your autogrow on the data file is insanely low. For a 300 GB database I'd set autogrow probably 5GB-10GB to start. See how long 10GB growth takes (minimal if instant initialisation is on) and adjust accordingly. You want a growth increment where it won't grow often and won't take ages to grow.

    The 10% on the log is also harmful. Autogrow should be in fixed increments (MB/GB), never %, especially for the transaction log where the cost of growing it is higher than for the DB.

    That's mostly academic though. Autogrow should never kick in. Autogrow should only be for the cases where the DBA messes up and the DB grows unexpectedly. Otherwise the growth should be done manually according to data volume growth and anticipated demand.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 14, 2011 at 1:54 am

    #1421091

    Thanx Gila for such nice guide.I am changing the settings to 2 GB.

    Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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