Post reply

truncating and sizing log files

  • February 2, 2016 at 2:49 am

    #399660

    Hi

    I've inherited a small number of databases and an SSIS ETL tool

    All works pretty well

    One question though

    Part of the build has a sizable impact on a specific databases transaction log file

    It grows to around 4 times the size of the database (approx. 80GB)

    That doesn't necessarily have a huge impact as I have plenty of disc space, it works, and looking at what it does it's expected

    Further down the line, there's a truncate all log files job (sp that's been created to work on every user database)

    This resizes the DB log back down to 1MB (with a 100MB autogrow)

    This makes no sense - or does it?

    Won't resizing down to 1MB then growing back to 80GB every night slow the process down (especially with 100MB autogrow) and, from what I have read, cause serious fragmentation?

    Is a more sensible approach to size at 80GB and leave?

    I'm aware, I should look at why it grow to 80GB and see if something can be done

    It's more a question of, how should the log files be sized and should they be truncated?

    Thanks

    Damian.

    - Damian

  • February 2, 2016 at 3:14 am

    #1855935

    DamianC (2/2/2016)

    Won't resizing down to 1MB then growing back to 80GB every night slow the process down (especially with 100MB autogrow) and, from what I have read, cause serious fragmentation?

    Yes.

    Is a more sensible approach to size at 80GB and leave?

    Yes

    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 2, 2016 at 3:17 am

    #1855936

    Thought as much

    Thanks for confirming!

    - Damian

  • February 2, 2016 at 7:35 am

    #1855978

    DamianC (2/2/2016)

    Hi

    Won't resizing down to 1MB then growing back to 80GB every night slow the process down (especially with 100MB autogrow) and, from what I have read, cause serious fragmentation?

    Is a more sensible approach to size at 80GB and leave?

    I'm aware, I should look at why it grow to 80GB and see if something can be done

    It's more a question of, how should the log files be sized and should they be truncated?

    Thanks

    Damian.

    To clarify, shrinking it and forcing it to regrow will slow the process down. The nasty fragmentation issues are issues with shrinking data files, not log files.

    Cheers!

  • February 2, 2016 at 8:40 am

    #1856008

    Just to add to what's already been recommended, how often do you take log file backups?

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

  • February 2, 2016 at 9:27 am

    #1856028

    In this instance, the DB is simple so I don't backup the transaction log

    - Damian

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

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