Correct way to truncate transaction log

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    steveb (2/5/2009)


    Ahmad Osama (2/5/2009)


    GilaMonster (2/5/2009)


    Ahmad Osama (2/5/2009)


    GilaMonster (2/4/2009)


    Why do you want to truncate and/or shrink your transaction log?

    I have a job to rebuild indexes..this increases the transaction log size....so i want to truncate it...

    I assume you mean shrink, since truncate does not change the size of the log.

    Why do you want to shrink the log? Free space in the log causes no problems. The log needs to be that size it it's growing that size so why not leave it?

    yes....I do mean shrunk....So...a transaction log backup after rebuild will do the job...

    No a transaction log back-up will truncate the log.. have a read of the links earlier in this thread.

    Also why are you worried about shrinking the log, give it room to grow otherwise it will end up the same size again

    GOT IT...

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • abishekshroff

    SSCrazy

    Points: 2040

    let me add some more

    backup log will truncate the log

    that means it will reuse the inactive portion of the transaction (few exceptions) log so it will prevent them growing

    However it will never return back the space of the inactive part of the log file to OS when you do a backup.So shrink is the only option...

    Changing the recovery model to simple and bring to full will help

    and its better than truncate_only as it has been deprecated in sql 2008..so better stop using them

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720983

    you don't want to shrink it. You'll just grow it when you reindex again. You want to have enough free space to handle normal data change needs, including rebuilding indexes.

  • maechismo_8514

    SSCoach

    Points: 17510

    Krishna (2/5/2009)


    I have a job to rebuild indexes..this increases the transaction log size....so i want to truncate it...

    How frequent do you rebuild your indexes. There will be heavy logging while rebuilding indexes. You would have to change your Recovery model to Bulk Logged recovery model while rebuilding the indexes.

    As adviced, you can set up a job to change the recovery model to bulk logged whenever you do indexes rebuild and after the process is done change ti back to Full Recovery model again.

    And on top of it if you can make some time, investigate to see whether you really have to rebuild them or reorganize them..........

  • Gail Shaw

    SSC Guru

    Points: 1004484

    nilmov (2/5/2009)


    Changing the recovery model to simple and bring to full will help

    But will break the log chain with all attendant risks and concerns.

    and its better than truncate_only as it has been deprecated in sql 2008..so better stop using them

    It was deprecated in 2005. It's removed completely in 2008.

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

    SSCrazy

    Points: 2040

    yes ..you are right..that was typo ..its sql 2005

Viewing 6 posts - 16 through 21 (of 21 total)

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