April 2, 2012 at 11:56 am
i have the log shipping setup on my server. since 2 weeks , i'm having performance issues on the production side. i noticed that the indexes are really fragmented. i need now to rebuild the indexes but i know that the indexes rebuilt increases the log file thus can impact the log shipping.the first time i did on my test environment, my log increase from 1GB to 25GB and i know that in production , it will be worst.the log shipping can not transfer this file on the network.
is there any recommendation to shrink the log after rebuilding indexes without breaking the log shipping?
April 2, 2012 at 12:45 pm
I'd suggest rebuilding the indexes in batches maybe doing the largest ones individually and the smaller ones all together.
April 2, 2012 at 1:10 pm
Don't shrink, it won't help with log backup sizes (and may make them larger as the shrink has to be logged, backed up and copied) and a large transaction log file does not hinder log shipping
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
April 2, 2012 at 1:19 pm
the problem is the log file size , i dont have enough space available for 20 or 25 gb because my log is always around 1GB. i can't leave it like that because i'm not sure if the log will reduce automatically since we're doing logshipping.i have to reduce but how to do that in good practice.
April 2, 2012 at 1:21 pm
KareM ,thx for your response but i cant either do that manually because there are lot of big indexes.it's not going to help me really.
April 2, 2012 at 1:24 pm
soulesidibe2011 (4/2/2012)
the problem is the log file size , i dont have enough space available for 20 or 25 gb because my log is always around 1GB. i can't leave it like that because i'm not sure if the log will reduce automatically since we're doing logshipping.i have to reduce but how to do that in good practice.
Add more space (if the log reaches 20 or 25 GB during regular operation or regular maintenance, then it needs to be 20 or 25 GB).
Rebuild your indexes in chunks and rebuild only what is fragmented not everything.
Switch to bulk-logged recovery for the duration of the index rebuilds if the data loss risk is acceptable.
Don't shrink (the log won't reduce in size automatically regardless of whether you are log shipping or not)
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
April 2, 2012 at 1:31 pm
i agree with you but i'have already ask for space but it's going to take time on client side. i need to do something now.
maybe, as you requested, i'll change to bulk logged mode.thx
April 2, 2012 at 2:43 pm
Don't change the recovery model permanently and check that the risks of bulk logged are acceptable before you do so.
If they are, switch to bulk logged before the index rebuilds and switch back to full afterwards.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply