February 25, 2010 at 12:06 pm
Hi,
I am running Log Shipping and when I rebuild the fragmented index my db and log files increase, so i have to shrink the .dmf and .ldf file, Can I shrink the database without breaking the log shipping
February 25, 2010 at 12:25 pm
You can, but you really shouldn't be considering shrinking the database at all.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
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
February 25, 2010 at 12:35 pm
Thanks for the reply,
But how to fix the problem, when ever i run the index job my .mdf and .ldf files increase. I don't want to break the LOGSHIPPING
.mdf is aound 100GB
.ldf is aound 200GB
February 25, 2010 at 1:02 pm
You could switch to bulk-logged for the duration of the index rebuilds. Otherwise, leave it alone, maybe increase the frequency of the log backups (the shipped ones) during the index rebuild.
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
February 25, 2010 at 1:44 pm
Thanks,
when I recreate an index, my Tran log backup also increase from 1 MB to 150 GB
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply