truncating and sizing log files

  • 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

  • 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
  • Thought as much

    Thanks for confirming!

    - Damian

  • 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!

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

  • 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