locked
Database Log File getting full by Reindex Job RRS feed

  • Question

  • Hey guys

    I have an issue with one of my databases during Reindex Job.  Most of the time, the log file is 99% free, but during the Reindex job, the log file fills up and runs out of space, so the reindex job fails and I also get errors from the DB due to log file space.  Any suggestions?

    Tuesday, April 14, 2015 2:03 PM

Answers

All replies

  • Its natural for tlog to grow when we perform reindex job. I would suggest to use smart script which will reindex as per FragmentationLevel:

    https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    Also take tlog backup in between reindex operation to free up space in tlog. 

    Find out large table run reindex and then run tlog backup this will help to free up log for next table reindex. You will have to work something like this to maintain space or add space for tlog to grow.

    • Edited by dave_gona Tuesday, April 14, 2015 2:17 PM
    • Proposed as answer by Shanky_621MVP Tuesday, April 14, 2015 4:52 PM
    Tuesday, April 14, 2015 2:09 PM
  • One thing you could try is to set your database to "BULK LOGGED" prior to the index rebuilds and setting it back to "FULL" after the index rebuilds.

    Are you rebuilding all of your indexes or only those based on a percentage of defragmentation? Is the size of your transaction log capped? After performing your database maintenance are you shrinking the Transaction Log?


    Please click "Mark As Answer" if my post helped. Tony C.

    Tuesday, April 14, 2015 2:10 PM
  • This is kind of a common issue. You should start using some smart reindexing solutions such that only the ones which are really required are reindexed.

    Even if that is not helping you, then the best option is to do the re-index on bigger tables/index on different days or time so that the log would get reused. 

    Read more about logging in rebuild/reorg

    http://social.technet.microsoft.com/wiki/contents/articles/24420.sql-server-curious-case-of-logging-in-online-and-offline-index-rebuild-in-full-recovery-model.aspx


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, April 14, 2015 2:20 PM
  • So the way we have it is we are rebuilding all databases, it's definitely not based on percentages.  The size of the log file is capped at 75GBs, but during most days it's 99% free, and we are not shrinking the file afterwards.
    Tuesday, April 14, 2015 2:32 PM
  • Thought so, you will have to change that. Instead of going for whole database do by fragmentation basis. This will help:

    https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


    Tuesday, April 14, 2015 2:50 PM
  • Please note changing to BULK LOGGED recovery will make you loose point in time recovery. Because alter index rebuild would be minimally logged and for the time period this job is running you loose point in time recovery so take step accordingly. Plus you need to take log backup after changing back to Full recovery

    I guess Ola's script would suffice if not you would have to increase space on drive where log file is residing. Index rebuild is fully logged in full recovery.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Tuesday, April 14, 2015 4:57 PM