Post reply

Huge Log File

  • February 19, 2009 at 9:00 am

    #201317

    Hello All

    I ran the following command on my server(production)

    dbcc sqlperf(logspace)

    For one of the databases, here is what I got

    DatabaseName LogSize log space used(%)

    COR_Online10960.55 0.99901920

    We backup transaction logs once every hour and do a full back up once every week.

    The datafile and the log file both have auto growth enabled and set to grow at 10%

    I am new to this environment and trying to clean up and any help is always appreciated.

    Thanks

    Shri

  • February 19, 2009 at 9:07 am

    #946823

    What's the max that the usage gets to in the course of normal operations?

    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

  • February 19, 2009 at 3:00 pm

    #947182

    As of now your log file is 10GB. So your 10% Growth will make the logfile grow 1GB more on every time it reaches its limit. Every hour it is truncating the log file to have room for the upcoming transaction that looks good to me. There is no need to cleanup or shrink this file, if you have the 10GB max usage of tlog…

  • February 20, 2009 at 9:26 pm

    #948097

    shri_sastry (2/19/2009)

    Hello All

    I ran the following command on my server(production)

    dbcc sqlperf(logspace)

    For one of the databases, here is what I got

    DatabaseName LogSize log space used(%)

    COR_Online10960.55 0.99901920

    We backup transaction logs once every hour and do a full back up once every week.

    The datafile and the log file both have auto growth enabled and set to grow at 10%

    I am new to this environment and trying to clean up and any help is always appreciated.

    Thanks

    Shri

    Dont set Autogrowth to such a marginal value. Set the Tlog file size to the maximum value that it can grow.

  • February 21, 2009 at 4:06 pm

    #948232

    Do you have any ETL processes on that server?

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

  • March 4, 2009 at 1:51 pm

    #953580

    Its advisable to set Auto file growth is in MB rather than in % for better performance , so if u keep as 100MB which is 10% of the 1GB which is currently exists.

    Note : If u perform frerquent bulk operations change recovery model of db to bulk-logged during the specific time alone

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

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