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.
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
Are you rebuilding all your indexes at the same time?
Are you rebuilding clustered indexes? If so, are you resorting the data?
Chris
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
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
ASKER
I forgot to add this last step.
4. Shrink the log file
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).....
This doesn't rebuild indexes....This rebuilt statistics (column sampling).....
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
https://www.experts-exchange.com/questions/20834222/DBCC-Showcontig-details.html?query=defragging+script&searchType=topic
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.