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

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.

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

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.

• Understanding Plan Guides (http://msdn.microsoft.com/en-us/library/dd672789(SQL.100).aspx)
• Troubleshooting Performance Problems in SQL Server 2008 (http://msdn.microsoft.com/en-us/library/dd672789(SQL.100).aspx)

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.

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:

• Backup Under the Bulk-Logged Recovery Model (http://msdn.microsoft.com/en-us/library/ms190692(v=SQL.105).aspx)
• Considerations for Switching from the Full or Bulk-Logged Recovery Model (http://msdn.microsoft.com/en-us/library/ms190203(v=SQL.105).aspx)

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.

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 (http://go.microsoft.com/fwlink/?LinkId=151500) for other considerations.