Link to home
Start Free TrialLog in
Avatar of km1039
km1039

asked on

transaction log backup too big after rebuilding indexes

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.  
Avatar of arbert
arbert

You can't get around this--it is a logged operation that's controlled internally.
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
Avatar of km1039

ASKER

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
Avatar of km1039

ASKER

I forgot to add this last step.

4. Shrink the log file
". use the DBCC UPDATEUSAGE sp to rebuild the indexes on the DB."

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

ASKER

Is there a sp that rebuilds the all the indexes for a particular DB?
Not built in....Do you need to rebuild, or can you get by with defragging?
I posted a script here a while back that will defrag your indexes (straight from books online):

https://www.experts-exchange.com/questions/20834222/DBCC-Showcontig-details.html?query=defragging+script&searchType=topic
Avatar of km1039

ASKER

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.

ASKER CERTIFIED SOLUTION
Avatar of arbert
arbert

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial