Post reply

T-log file blows up during reindexing - how to prevent this?

  • January 11, 2012 at 7:34 am

    #249494

    I am on SQL server 2008 R2 and have a database with recovery Model Full. I have a maintenance plan to back up t-log files in every 15 minutes from (6:00 AM - 3 :00 AM) Then at 3:00AM the Full backup job kicks in. I perform the reindexing at 1:00 AM. The reindexing seems to blow up the log file to 24 GB whereas the actual t-log size ( from the 15 mins backup) is not more than 200 mb. How can I prevent the t-log file from blowing up? I have looked for this answer all over the internet but have not found any solid resolution for it. Any pointers would be greatly appreciated. Thanks

  • January 11, 2012 at 7:51 am

    #1431799

    Bulk-logged recovery (if you can accept the minor risks to your your ability to restore the DB to point-in-time). Rebuild only what needs rebuilding, not everything.

    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

  • January 11, 2012 at 8:21 am

    #1431829

    GilaMonster (1/11/2012)

    Rebuild only what needs rebuilding, not everything.

    You could use Ola Hallengren's[/url] maintenance solution for this particular point.

    It's a great script.

    -- Gianluca Sartori

Viewing 3 posts - 1 through 2 (of 2 total)

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