23 April,2013 by Jack Vamvas
“Can I commit an Index Rebuild without logging?” is a regular question from developers and DBAs.
Most DBAs run regular index maintenance scripts .One common problem is managing the Transaction Logs file growth during the procedure. Typical problems caused by sql log growth include running out of disk space. Therefore, the question related to Index Rebuild without logging .
SQL Server Transaction Logs record information in case of rollback. For example, the ALTER INDEX statement below will drop all indexes and rebuild in one transaction. If the indexes are very large and there are a large amount of extents changes, this will impact disk requirements. The Active Log needs to grow to accommodate a rollback in case of error.
The impact on transaction logs is :
The start and finish of the transaction
All extent allocation and deallocation
Creating\dropping of index
ALTER INDEX ALL ON Database1.Table1 REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
It is not possible to commit an Index Rebuild without some impact on Transaction Logs. It is possible to change the Recovery Model to Bulk Logged or Simple which allows minimal logging, but that does not stop logging.
There a good reasons for the requirement. Time is better spent managing the Index Rebuild process – focusing on techniques to Manage Transaction Logs during Index Rebuild