Index rebuild generate huge log file makes logshipping and mirroring out of sync - by Shamik2

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 352338 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 6/21/2008 2:21:46 PM
Access Restriction Public



I believe SQL Server 7.0 has a feature that will not update log file while index rebuild. Since SQL 2000 this feature disables and problem starts. I am working in large SQL server 2000 and 2005 environment and have three scenarios to describe this issue.

[1] Having near to 1000 SQL servers, it is difficult to rebuild selected indexes on tables. We generally rebuild all indexes on week-end maintenance job. Because of large size database, the log file grows in huge size and fills up the disk drives. The rebuild index job failed many times because of space issue on log file, tempdb etc. The rebuild index script need to change recovery model to simple before index rebuild starts and this opens a door for tragedy. Financial data can’t afford to loose transaction and this happen many times as after rebuild index, for some reason, the database stays in simple recovery model.

[2] In log shipping, the huge log files generated after index rebuild job create a big issue on WAN link. Many times the secondary server goes out of sync because of the large log files chocked up the WAN link. Here I can’t change the Primary database to simple recovery model.

[3] The issue happens with log shipping repeats with database mirroring.

Sign in to post a comment.
Posted by Microsoft on 11/15/2009 at 4:23 PM

My apologies for the delayed response. Essentially,you are looking for minimally or non-logged index builds so that you don't need huge log space. But clearly, this does not work for log shipping or database mirroring which require full logging. Based on your point, you already identified that hese indexes need to be rebuild for performance reasons.

SQL2008 mirroring provides compression for the log sent to teh mirror. So please try that and see if it works for your situation

Second thing to consider will be to see if you can partition your tables and have aligned indexes. This way you can bring down the size of indexes.

I am closing this as by design but please feel free to re-activate if you are not satisfied with the explanation.

Thanks for interest in SQL Server

Posted by Shamik2 on 6/30/2008 at 7:57 PM
Thanks for comment.
Majority of indexes are built after good consideration and practically checked the high performance issue when not rebuild them.
I dont understand why should I need log files of index rebuild process? Again, I have stated, I am working on large number of sql server deployment. Each instance have 25 -30 databases and again total instances are near to 1000. Many of the databases are of large size and thus creating this issue. I dont want to change database recovery model every week to simple. Any solution to avoid large log files?
Posted by Microsoft on 6/23/2008 at 10:57 AM
Have you tried the new feature in database mirroring that ships compressed log? Agreed, this does not address you concern for log shipping. Since database mirroring and log shipping keeps data upto date at physical level, it needs to be fully logged. I am interested in knowing why you are rebuilding indexes weekly? Assume it is for removing fragmentation. Do you know if the indexes are indeed fragmented and if yes, is it impacting your query performance?