October 18, 2006 at 3:40 pm
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
October 19, 2006 at 4:51 am
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.
October 19, 2006 at 11:38 am
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
October 24, 2006 at 12:42 pm
You might want to flip your DB in a Simple recovery mode for completing reindexing.
October 24, 2006 at 3:03 pm
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
October 25, 2006 at 9:52 am
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
October 25, 2006 at 11:27 am
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
October 25, 2006 at 11:56 am
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
October 26, 2006 at 3:35 am
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