Correct way to truncate transaction log

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    Hi,

    What is the correct way to truncate trucate transaction Log..truncate_only or dbcc shrinklog after transaction log backup???

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

  • GSquared

    SSC Guru

    Points: 260824

    The correct way to truncate the transaction log is run a log backup.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • JestersGrind

    SSCertifiable

    Points: 5439

    It sounds like you're talking about two different things. Like GSquared said, truncating occurs automatically after you backup the log file. Shrinking the size of the log file can be done through DBCC SHRINKFILE or DBCC SHRINKDATABASE.

    Greg

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    GSquared (2/4/2009)


    The correct way to truncate the transaction log is run a log backup.

    okies...however some time it just didn't work..in those cases I hv used dbcc shrinklog to truncate log.....

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

  • Gail Shaw

    SSC Guru

    Points: 1004484

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

    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
  • Gail Shaw

    SSC Guru

    Points: 1004484

    Ahmad Osama (2/4/2009)


    in those cases I hv used dbcc shrinklog to truncate log.....

    Shrink file will never truncate the log. It shrinks the file. Truncate refers to discarding old log records and only a log backup, a log truncation or (in simple recovery only) a checkpoint will truncate the log.

    That said, a log backup will never shrink the log file.

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

    SSCertifiable

    Points: 5439

    Recommended reading:

    Transaction Log Truncation

    Shrinking the Transaction Log

  • sayfrend

    SSCommitted

    Points: 1733

    Another way to truncate logfile without taking backup is,

    DBCC sqlperf ('logspace')

    check for the %used column for your database

    do the following,

    Step1: Change your Recovery Model from Full to Simple

    Step2 : Change your Recovery Model from Simple to Full.

    DBCC sqlperf ('logspace')

    check for the %used column for your database

    Thats it, But I don;t know if this is a good practice to truncate log ?

    Experts please comment.

    Thanks,

    IM.

  • GSquared

    SSC Guru

    Points: 260824

    sayfrend (2/4/2009)


    Another way to truncate logfile without taking backup is,

    DBCC sqlperf ('logspace')

    check for the %used column for your database

    do the following,

    Step1: Change your Recovery Model from Full to Simple

    Step2 : Change your Recovery Model from Simple to Full.

    DBCC sqlperf ('logspace')

    check for the %used column for your database

    Thats it, But I don;t know if this is a good practice to truncate log ?

    Experts please comment.

    Thanks,

    IM.

    Basically, do this kind of thing only if you don't care about the data in your database. If you do any of these methods for truncating the log other than backing it up, you risk losing data, because the log is part of what you can use to restore a crashed database.

    If losing what's in the transaction log is fine for a given database (sometimes it is, and point-in-time restore is unimportant), then the database should be in simple recovery mode, and you won't have to truncate the log because SQL Server will do it for you.

    Shrinking the log is, usually, a waste of time. Most times, it grew because it needed the space, and shrinking it just means it's going to grow again. Not always, but most of the time. Same goes for shrinking the data files. Usually a bad idea.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Jason Williams-387288

    SSCommitted

    Points: 1740

    GSquared (2/4/2009)


    Shrinking the log is, usually, a waste of time. Most times, it grew because it needed the space, and shrinking it just means it's going to grow again. Not always, but most of the time. Same goes for shrinking the data files. Usually a bad idea.

    I have read a lot about the transaction log lately and I have noticed a few reoccuring themes.

    Size your transaction log as best as you can. It will continue to grow to what it needs.

    Your log backups truncate the log, not shrink it. Big difference.

    Only shrink transaction log when you have to. Otherwise, it will grow right back to the size it needs. 🙂

    I did read one article where it mentioned trying to size the transaction log accordingly. This article in particular I found interesting:

    http://sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

    Hopefully it helps the OP.

    Cheers,

    Jason

  • Ahmad Osama

    SSCertifiable

    Points: 7676

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

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

  • maechismo_8514

    SSCoach

    Points: 17510

    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.

    Did you check the fragmentation levels before re building your indexes, may be you can reorganize them?

    And Truncating transaction logs is a very bad practice as advised before. It will break the log chain and you would never be able to achieve point-in-time recovery. On top of it if you truncate the tlog you would have to take a full backup and then transaction logs to maintain the log chain.

  • Gail Shaw

    SSC Guru

    Points: 1004484

    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?

    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
  • Ahmad Osama

    SSCertifiable

    Points: 7676

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

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

  • steveb.

    SSC-Forever

    Points: 46733

    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

Viewing 15 posts - 1 through 15 (of 21 total)

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