You may notice an increased transaction log sizes in SQL Server 2008 and later versions when you perform Index Maintenance

Article ID: 2407439 - View products that this article applies to.
Expand all | Collapse all

SYMPTOMS

Consider the following scenario:
  • You have an instance of SQL Server 2005 wherein you routinely perform an online index rebuild operation on an index in a database (using the ALTER INDEX statement with the ONLINE option set to ON).
  • The database uses FULL recovery model.
  • You upgrade this instance of SQL Server to either SQL Server 2008 or SQL Server 2008 R2 and you attempt the same operation on the index. 

In this scenario, you will notice that, in the newer version, the transaction log for the database expands at a much higher rate than in the older version for the same operation.
As as a result of the increased transaction log size, utilities such as log shipping, database mirroring, transaction log backups, and transactional replication may run slower than they used to in SQL Server 2005.

CAUSE

Online Index Rebuild is a fully logged operation on SQL Server 2008 and later versions, whereas it is minimally-logged in SQL Server 2005. The change was made to ensure data integrity and more robust restore capabilities. . 

RESOLUTION

Consider reducing the frequency of online index rebuild operations. To ensure optimal index usage and performance, update statistics more frequently rather than reorganizing the indexes. Also, if query plan stability is an issue, consider using plan guides or other techniques for tuning performance. Please refer to the following links for further information.
You can also consider rebuilding the indexes offline.To rebuild indexes offline, the ALTER INDEX statements have the ONLINE option set to OFF .

The preferred method depends on your business requirements.

MORE INFORMATION

You may want to evaluate your current storage capacity to ensure that it can accommodate the increased size of transaction log in the SQL Server versions later than SQL Server 2005. In addition, Microsoft recommends that administrators test applications that use Log Shipping, Database Mirroring and Transactional Replication in order to establish new baselines for how fast those utilities will perform on SQL Server 2008 or later versions.

If log size needs to be controlled due to storage capacity limitations, you may consider using the BULK LOGGED recovery model for the duration of the online index rebuild. Please refer to the following MSDN articles to understand the pros and cons of using the BULK LOGGED model:


Note that under Bulk Logged recovery the transaction log size may be smaller when minimally-logged operations are performed, but the actual transaction log backup size may still be large. This is because the log backup captures all the database pages changed by the minimally-logged operations.


REFERENCES

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Properties

Article ID: 2407439 - Last Review: May 24, 2011 - Revision: 5.0
APPLIES TO
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Enterprise Evaluation
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Express with Advanced Services
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Standard Edition for Small Business
  • Microsoft SQL Server 2008 R2 Web
  • Microsoft SQL Server 2008 R2 Workgroup
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2008 R2 Express with Advanced Services
Keywords: 
KB2407439

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com