SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Manage Transaction Logs during Index Rebuild

03 April,2013 by Jack Vamvas

DBA manages regular index maintenance scripts . There are many variations – usually based around – REORGANIZE and then at a higher fragmentation level do a INDEX REBUILD. One common problem is managing the Transaction Logs file growth during the index rebuild procedure.

The Transaction Log records relevant information in case of rollback. If a large amount of extents change , these are recorded and can create very large Transaction Logs

 As a general guide the minimum amount of transaction log space required is the size of the largest table.  An Index Rebuild will rebuild the whole table in on etransaction

Some ideas to manage Transaction Log growth during Index Rebuild:

1)       Take more Regular log backups . Consider increasing the frequency during the Index Rebuild phase

2)       Create proper sizings for the transaction log file

3)       Split up the Index Rebuilds over different maintenance windows. If  the database has a number of large tables – than restructure the maintenance windows

4)       Increase the threshold to trigger the Index Rebuild . In other words, commit REORGANIZE up to a higher level of frgementation

Read More

SQL Server Disable Indexes and Rebuild Indexes dynamically

SQL SERVER REBUILD INDEX

SQL Server - Disable Index and Rebuild Index for Updates on very large tables

Predict ALTER INDEX REORGANIZE finish time - SQL Server DBA






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