transaction log backup too big after rebuilding indexes

Posted on 2004-04-12
Last Modified: 2008-03-17
I know that rebuilding indexes on a database is a logged operation and that is why the transaction log backup that occurs ofter i rebuild the indexes is so large but I wanted to know how to get around logging this operation.  Is it wise to not log this operation?  Space is an issue on my server and the transaction log backup is around 4 gigs when this occurs.  
Question by:km1039
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
LVL 34

Expert Comment

ID: 10806698
You can't get around this--it is a logged operation that's controlled internally.

Expert Comment

ID: 10806699
How are you rebuilding the indexes? (dbcc, drop and create etc?)
Are you rebuilding all your indexes at the same time?
Are you rebuilding clustered indexes? If so, are you resorting the data?


Author Comment

ID: 10806747
The indexes are rebuilt using SQLMaint in a DB maintenance plan.
All indexes for a particular DB are rebuilt in one operation.
I assume SQLMaint is resorting the table on the clustered index.

I thought I could create a scheduled job that does the following:
1. backups up the trans log file.
2. use the DBCC UPDATEUSAGE sp to rebuild the indexes on the DB.
3. Backup the log with the no_log option right after the rebuild is complete
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.


Author Comment

ID: 10806755
I forgot to add this last step.

4. Shrink the log file
LVL 34

Expert Comment

ID: 10806812
". use the DBCC UPDATEUSAGE sp to rebuild the indexes on the DB."

This doesn't rebuild indexes....This rebuilt statistics (column sampling).....

Author Comment

ID: 10806836
Is there a sp that rebuilds the all the indexes for a particular DB?
LVL 34

Expert Comment

ID: 10806851
Not built in....Do you need to rebuild, or can you get by with defragging?
LVL 34

Expert Comment

ID: 10806860
I posted a script here a while back that will defrag your indexes (straight from books online):

Author Comment

ID: 10807001
Could you explain the difference between rebuilding and defragmenting?

The "Reorganize data and index pages" in the maintenance plan causes the tranascation log to grow so much becuase it drops and recreates all the indexes.  If I decided just to defrag the indexes will that fix my problem with the log file growing.

LVL 34

Accepted Solution

arbert earned 500 total points
ID: 10807556
Do you have access to Books Online?  This is straight from BOL:

DBCC INDEXDEFRAG can defragment clustered and nonclustered indexes on tables and views. DBCC INDEXDEFRAG defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes, thus improving index-scanning performance.

DBCC INDEXDEFRAG also compacts the pages of an index, taking into account the FILLFACTOR specified when the index was created. Any empty pages created as a result of this compaction will be removed. For more information about FILLFACTOR, see CREATE INDEX.

If an index spans more than one file, DBCC INDEXDEFRAG defragments one file at a time. Pages do not migrate between files.

Every five minutes, DBCC INDEXDEFRAG will report to the user an estimated percentage completed. DBCC INDEXDEFRAG can be terminated at any point in the process, and any completed work is retained.

Unlike DBCC DBREINDEX (or the index building operation in general), DBCC INDEXDEFRAG is an online operation. It does not hold locks long term and thus will not block running queries or updates. A relatively unfragmented index can be defragmented faster than a new index can be built because the time to defragment is related to the amount of fragmentation. A very fragmented index might take considerably longer to defragment than to rebuild.  In addition, the defragmentation is always fully logged, regardless of the database recovery model setting (see ALTER DATABASE). The defragmentation of a very fragmented index can generate more log than even a fully logged index creation. The defragmentation, however, is performed as a series of short transactions and thus does not require a large log if log backups are taken frequently or if the recovery model setting is SIMPLE.

Also, DBCC INDEXDEFRAG will not help if two indexes are interleaved on the disk because INDEXDEFRAG shuffles the pages in place. To improve the clustering of pages, rebuild the index.

DBCC INDEXDEFRAG is not supported for use on system tables.

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question