Post reply

Log backup is huge even when the actual log file size is very small

  • December 7, 2014 at 9:31 pm

    #312959

    Hi All,

    SQL Server 2005 std. edition

    SP4

    Facing a strange issue.

    One of the log backup is about 35gb at a particular time, after the index rebuild. Index rebuild process takes only a few minutes.

    Recovery mode during rebuild is bulk-logged and there is one table with more than 20 million records.

    What's confusing is, in the standard report for data usage it clearly shows the log file was only few MBs and it has never grown in the past few days. And also I had set up a job which captures the log file size and used log space, which also shows the log file was few MBs only through out.

    Then how come the log backup size so huge during that particular time, when it's actual size was few MBs ?

    Has anyone faced this issue ever ?

    Thanks in advance.

    San.

  • December 18, 2014 at 3:54 am

    #1765391

    is it the case that the physical size of the ldf file on disk is over 35gb, however after a log backup only a small amount of this file is in use until after the index rebuild? does your log backup go soon after the index rebuild?

  • December 18, 2014 at 5:24 am

    #1765426

    Are you appending to the file when taking backups?

    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 3 posts - 1 through 2 (of 2 total)

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