Log file Growth

  • John Mitchell-245523 (8/4/2016)


    What do you mean by "only"? What log file growth are you seeing? What other index rebuilds occur in the hour between the previous transaction log backup and the next?

    John

    Hi John,

    Log file size increased from 1-10GB after the index rebuild job and the size is indexes involved are 5GB all together, its a weekly job .

  • Twice as much space as you need for all your indexes - is that a big problem? What is the autogrowth increment for the log file? Was there any other activity on the database at the same time, just before or just after? You might find this useful.

    John

  • GilaMonster (8/1/2016)


    A rebuild of a 10GB index will require more than 10GB of log space.

    Because there's other log records involved, and any other concurrent activities, and log reservations, and...

    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
  • GilaMonster (8/4/2016)


    GilaMonster (8/1/2016)


    A rebuild of a 10GB index will require more than 10GB of log space.

    Because there's other log records involved, and any other concurrent activities, and log reservations, and...

    Thanks Gail.

  • VastSQL,

    Another possibility for dealing with this issue is to follow advice from this MSDN article:

    Specifically:

    "Consider setting the SORT_IN_TEMPDB option to ON for the index operation. This separates the index transactions from the concurrent user transactions. The index transactions will be stored in the tempdb transaction log, and the concurrent user transactions will be stored in the transaction log of the user database. This allows for the transaction log of the user database to be truncated during the index operation if it is required. Additionally, if the tempdb log is not on the same disk as the user database log, the two logs are not competing for the same disk space."

    Hope this helps!!

    Jon

Viewing 5 posts - 16 through 19 (of 19 total)

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