Solved

transaction log backup too big after rebuilding indexes

Posted on 2004-04-12
10
726 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
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.

 

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 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.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Generate Scripts Fails 5 36
Convert formula to max at a specific date by month 18 19
SQL Server Designer 19 40
Find SQL query used by application 3 19
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

773 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