transaction log is full because of reindex job

  • sqlfriend

    SSC Guru

    Points: 52433

    I have a weekly reindex job for all databases in maintence plan.

    Sometimes it failed with the message transaction log is full.

    We do have a transaction log back up before it 1 hour ago.

    And now when I check it the transaction log is 99% free.

    This because after the reindex, the hourly transaction log back up ran.

    How can we avoid this problem of the reindex makes transaction log full?

    Thanks

  • G.R.Prithiviraj Kulasingham

    SSCarpal Tunnel

    Points: 4104

    There are two ways of handling this situation

    1. Backup transaction log in between

    2. Are you taking a full backup after this task? Then you can change your recovery option to Bulk log or even simple during this operation and then revert that change.

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • sqlfriend

    SSC Guru

    Points: 52433

    Thanks a lot.

    So in 1. I can do the transaction log backup during the process of reindex?

    2. I do have a full backup, but it is on next day.

    So you ask if I have a full backup, does it mean I should do a full backup right after the revert of recovery ?

    We do have transaction log back up after that, will that work?

  • Gail Shaw

    SSC Guru

    Points: 1004474

    sqlfriends (9/17/2011)


    So you ask if I have a full backup, does it mean I should do a full backup right after the revert of recovery ?

    We do have transaction log back up after that, will that work?

    A log backup is sufficient. Just familiarise yourself with the bulk-logged recovery model first and what it means for point-in-time restores. Also note that while your log won't grow, the log backup will be the same size or larger.

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

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