Home Forums SQL Server 2005 Administering Transaction log is not truncated even after log backup in FULL recovery model RE: Transaction log is not truncated even after log backup in FULL recovery model

  • naren.ece2012 (10/15/2016)


    Actually in our environment, we have 310 Gb of production database in that we have 200 Gb of data files and 110 GB of log files.The log space has been increased due to the rebuild index JOB ran. The log backup is taken every onehour, but the log space is not truncated.Kindly help on this, i am new to DBA.

    Regards

    NM

    You're mixing up some terms. "Log truncation", for most of us, means that data in the transaction log file has been deleted because whatever transaction caused it has been completed and committed AND a log file backup has occurred. That process does not decrease the size of the log file.

    What you're talking about is the "shrinking" of the file-space used and that doesn't happen automatically just because data has been deleted from the log file.

    If this is a regular problem (and, for restore purposes, I do agree that the log file is much bigger than it should be), consider temporarily switching the database to the "BULK LOGGED" Recovery Model during the index rebuilds of large tables so that you can take advantage of "Minimal Logging". There is a caveat to that, though. You need to read up on the effect that minimally logged operations can have on Point-in-Time restores.

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