Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

transaction log backup too big after rebuilding indexes

Posted on 2004-04-12
10
Medium Priority
?
737 Views
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.  
0
Comment
Question by:km1039
  • 5
  • 4
10 Comments
 
LVL 34

Expert Comment

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

Expert Comment

by:ChrisFretwell
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?

Chris
0
 

Author Comment

by:km1039
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:km1039
ID: 10806755
I forgot to add this last step.

4. Shrink the log file
0
 
LVL 34

Expert Comment

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

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

Author Comment

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

Expert Comment

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

Expert Comment

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

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20834222.html?query=defragging+script&searchType=topic
0
 

Author Comment

by:km1039
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.

0
 
LVL 34

Accepted Solution

by:
arbert earned 2000 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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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 SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

916 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