Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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

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

View products that this article applies to.

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.

↑ Back to the top

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. . 

↑ Back to the top

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.

↑ Back to the top

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.


↑ Back to the top

Keywords: kb

↑ Back to the top

Article Info
Article ID : 2407439
Revision : 1
Created on : 1/7/2017
Published on : 10/30/2015
Exists online : False
Views : 401