ApexSQL Knowledgebase: What to do with large log files due to index rebuilds

LINKS

General

Apex SQL Audit
Apex SQL Clean
Apex SQL Code
Apex SQL Diff
Apex SQL Doc
Apex SQL Edit
Apex SQL Enforce
Apex SQL Log
Apex SQL Script

Knowledgebase Home

SITE FEED

Site Feed

Support Forum

The fastest and most effective to get answers to your questions quickly. Click Here for more info.

FAQs

Quick answers to common questions. Click Here for more info.

Troubleshooting

Known issues and basic troubleshooting techniques for problems or unexpected behaviors. Click Here for more info.

Knowledgebase

Technical tips, How-to articles, and other tutorials about ApexSQL Tools. Click Here for more info.

Online Documentation

All of our help files for download or convenient viewing online. Click Here for more info.

Videos

Watch product demos, training videos, or tutorials of our products' main features. We frequently upload new videos so check often for updates. Click Here for more info.

ApexSQL Knowledgebase

Tips and How-to Articles for Apex SQL Tools

What to do with large log files due to index rebuilds

DESCRIPTION
Index rebuilds can create very large log files. This KB discusses what to do when this happens.

SOLUTION
1) Make sure the database has a LOT of empty space in it. As in 1 to 2 YEARS worth of estimated growth if it is an active database, or double current space if it is slowly changing. This will allow the index rebuild/reorg process to lay the data/index pages down in contiguous order - as in not fragmented right off the bat. Trying to defrag a full database is a fruitless excercise!

2) Defrag only stuff that is currently highly fragmented. The standard for this is > 30%. See BOL for a script you can use plug-and-play. I also skip very small tables (< ?? pages in size, where ?? is either 50 or 100 depending on the phase of the moon :)

3) Assuming you have a very fragmented system due to not having sufficient space, open up free space and then CONTROL the first few rebuilds. Split up your tables into different runs so as not to create a huge log at one time. Be sure to backup the tlog between runs to flush committed entries and free space.

SEE ALSO
Excessive DAT Files

AUTHOR
TheSQLGuru

LAST REVIEW
30 August 2007

Labels: ApexSQL Log, General

posted by Janice Lee on Monday, July 16, 2007

0 Comments: