Post reply

transaction log

  • July 21, 2010 at 4:23 am

    #226983

    in sql server 2008 when ever we take backup of transaction log, does it truncate the transaction log?????

  • July 21, 2010 at 6:10 am

    #1196093

    If the database is in full/bulk-logged recovery, a log backup marks the inactive portion of the log as reusable (sometimes referred to as truncating the log). It does not affect the size of the log file on disk, just the used/free status of the space within 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

  • July 23, 2010 at 11:22 am

    #1197648

    First run this query

    select log_reuse_wait_desc from sys.databases where name= 'dbname'.

    If it returns log backup take 2 continuous log backups to truncate the log .

  • July 24, 2010 at 2:55 am

    #1197844

    it is showing 'NOTHING' thn??

  • July 24, 2010 at 5:52 am

    #1197851

    So there is nothing preventing log reuse and the log should not be currently growing.

    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

  • May 21, 2015 at 6:42 am

    #1799636

    yes it truncates the transaction log whenever we take a backup of transaction log.

    like if the database is in simple recovery model, once it reaches checkpoint it autotruncates.

    whereas in full recovery or bulklogged recovery model, once it reaches checkpoint, log backup has to be taken and then the inactive vlf's get truncated.

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

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