Indexing and TranLogs

  •  

    I am having weird problem.

    First I tried to DBCC REINDEX on some large table and it runs and stops with Trans Log full. I increased the size of the log and it does it again.

    Second Problem..I backup the log and set the LOG limit to 7000 MB. After a while I run the DBCC SQLPERF(LOGSPACE) and it tells me limit is 1500 MB..why it is decreasing even when I have set the limit. and TranLog  is not set to autogrow.

    How can I complete the REINDEX job without being logs getting filled??

    Dire need of help....Production system needs performance.

    Thanks

    TB


    Tajammal Butt

  • What are the size of the indexes that you are using?

    Also do the errors tell you which T-log is running out of space?

    I think the TempDB one gets used as well especially if you use the SORT IN TEMPDB option.

  • The error is saying that production DB log is getting full. I am not sure about the tempDB part..Can u please elaborate..but I will read on it too..Sometime explaining work better.

    thanks


    Tajammal Butt

  • You might want to flip your DB in a Simple recovery mode for completing reindexing.

  • Good idea...I would have given that a shot..But I am back from my assignment. Will pass it on and see how it works..


    Tajammal Butt

  • Using DBCC DBREINDEX causes the log file to grow up to 2.5 times the size of the database. It has to keep track of the 'state of the database' in case it has to rollback the reindexing.

    -SQLBill

  • That is nuts...so what do we do when we have 135GB database. What are some of the strategies we can use to reindex the large tables??Any suggestions??/


    Tajammal Butt

  • I had a 300+ GB database (just changed jobs) and I did DBCC INDEXDEFRAG. It doesn't grow the tlog as large as doing the reindexing. Plus, if I had to stop the job, it didn't rollback the changes.

    -SQLBill

  • Another strategy you might try is to reindex each index individually rather than the whole table.

    If there is a clustered index then it may be the re-indexing of that which is causing the issue.

    Make sure you monitor the log for each one and perform a log backup to truncate the log.

Viewing 9 posts - 1 through 8 (of 8 total)

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