You may notice an increased transaction log sizes in SQL Server 2008 and later versions when you perform Index Maintenance
- 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.
The preferred method depends on your business requirements.
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
- Considerations for Switching from the Full or Bulk-Logged Recovery 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.
Article ID: 2407439 - Last Review: 10/30/2015 22:38:00 - Revision: 6.0