Solved

transaction log backup too big after rebuilding indexes

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

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now