Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Index Rebuild without logging

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

Read More

Modify sql transaction log file size - increase or decrease

SQL Server – Monitor SQL Transaction Log AutoGrowth for performance issues

SQL Server - Inside the sql transaction logs with fn_dblog and current transaction_id

SQL Server – Read sql transaction Logs with ::fn_dblog








Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer