Post reply

Will shrinking the transaction log reduce the size of a full backup?

  • October 10, 2012 at 12:39 pm

    #265520

    Will shrinking the transaction log reduce the size of a full backup?

  • October 10, 2012 at 12:42 pm

    #1547783

    Depends on what you mean by "shrinking". If you mean truncating it, to "shrink" the data in it, then yes. If you mean shrinking the file but leaving the data alone (just getting rid of some free space in it), then no.

    Keep in mind that truncating the transaction log is almost always a really, really 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

  • October 10, 2012 at 2:00 pm

    #1547799

    OK that's what I thought. The T-log grew due to a bad query so now it's >1% used. I was hoping I could shrink to reduce the backup size. Oh well, I'll have to get the vendor to archive some data.

    Thanks!

  • October 10, 2012 at 9:25 pm

    #1547868

    Jon.Morisi (10/10/2012)

    OK that's what I thought. The T-log grew due to a bad query so now it's >1% used. I was hoping I could shrink to reduce the backup size. Oh well, I'll have to get the vendor to archive some data.

    Thanks!

    When's the last time you did a full rebuild of all indexes?

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

  • October 10, 2012 at 9:58 pm

    #1547872

    They're done weekly. I'm actually working through testing ola's scripts on it.

  • October 11, 2012 at 2:14 am

    #1547928

    No, neither shrinking the log nor explicitly truncating it will reduce database backup size. The portion of the log included in a full backup is usually just from the start of the oldest open transaction at the point the full backup starts until the end of the backup (can be more if there's replication, CDC or async mirroring)

    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

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

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