Home Forums SQL Server 2005 Backups tran log backup confused 2.5gb log file but 70gb log backup size RE: tran log backup confused 2.5gb log file but 70gb log backup size

  • lawson2305 (7/16/2012)


    opc.three (7/16/2012)


    I assume you have autogrow enabled on your log file. Check the default trace to see when it grew, that can help establishing a timeframe of when the log bloat started, and hopefully mostly occurred:

    Reviewing AutoGrow events from the default trace by Aaron Bertrand

    ok from this script I do see a pattern.

    I'm guessing this script tells me everytime the file grows.

    <truncated content>

    I'm guessing what was running at process 77 is my issue. I do run maintenance task at this time I will review the history.

    The default trace records each time a file autogrow operation. The default trace is always running by default, but rotates through a fixed number of files so older events will eventually fall off the set of files.

    I am happy you caught the pattern. If you need point-in-time recovery there isn't much you can do in terms of reducing the total size of your log backups, but you can manage the size of each one by taking them more frequently while you're doing index maintenance.

    Is your 'maintenance task' running a SQL Server Maintenance Plan (MP) doing index rebuilds? One thing I see a lot is people rebuilding ALL their indexes at one time using a MP which is wasteful because it rebuild all indexes regardless of whether they need it or not. This practice is a common source of log bloat.

    If you're using an MP consider checking into an index maintenance solution that only rebuilds or reorganizes indexes based on their level of fragmentation, i.e. only when they need it. Here is one I use and recommend often:

    SQL Server Index and Statistics Maintenance by Ola Hallengren

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato