DBCC Reindex - Issues with trans log growth

Question

DBCC Reindex - Issues with trans log growth

Viewing 10 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Vik,

Did the backup for the T log also include a backup for the database? If it did that could account for the drastic change in backup size.

Just something else to look at

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Vik Nat Hall of Fame Points: 3930 More actions · Answer 2

Nope, I just backed up the trans log. Anyone got any ideas on this?

ray_higdon SSCarpal Tunnel Points: 4589 More actions · Answer 3

Are you able to reproduce this? And are you saying your .trn backup is 15gb or your LDF?

------------

Ray Higdon MCSE, MCDBA, CCNA

Vik Nat Hall of Fame Points: 3930 More actions · Answer 4

Hi Ray,

Please see above for details - no, my trans logs is fine and is backing up every hr and the size is now normal ( growing to 100 MB every hr). The problem I had was earlier this week, when I put the db to the bulk logged recovery model and ran the DBCC REINDEX - See above for details. Has anyone encountered this before?

Vik.

Vik Nat Hall of Fame Points: 3930 More actions · Answer 5

Have not had a response from anyone. Please let me know if anyone has any thoughts on this.

Thanks,

Vik.

Stephen Jiang Valued Member Points: 53 More actions · Answer 6

Here is some comments from reading the initial question:

Since log shipping is also involved in the server, the only alternative is to use “bulk_logged” recovery model for your DBCC DBREINDEX operation. This will save you some log spaces comparing to “full” recovery model.

Note that DBCC DBREINDEX uses one big transaction, killing the operation in the middle would rollback the entire transaction. It would not help you reduce log space consumption if you restart the index rebuild after killing.

I noticed that you were doing index rebuild monthly. DBCC DBREINDEX is an offline operation. While doing clustered index rebuild, the entire table is offline. You should only consider rebuilding index if fragmentation affects your query performance.

If after a while, your query performance goes down because of fragmentation, do you consider using DBCC INDEXDEFRAG to reduce fragmentation and improve space usage?

Three biggest advantages of DBCC INDEXDEFRAG are

(1). If fragmentation is low (under 20%), you will use less log space than DBCC DBREINDEX;

(2). DBCC INDEXDEFRAG is an online operation and uses in-place page reorganization algorithm, hence space requirement for data is almost none and other query could access the data while defrag is going on;

(3). You can kill and restart in the middle of defrag and all finished work is preserved.

The major drawbacks if DBCC INDEXDEFRAG are

(1). It is slower than DBCC DBREINDEX if fragmentation is high;

(2). Index statistics are not updated after DBCC INDEXDEFRAG.

For detail comparison, you can read the white paper in the following location: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

This posting is provided "AS IS" with no warranties, and confers no rights.”

gjackson SSC Veteran Points: 244 More actions · Answer 7

"When I ran the transaction log backup after putting the DB to FUll Recovery model, I noticed that the backup was huge - 15GB. How did that happen? How did it get so big - the trans log during the reindexing only grew to a 300MB as I was keeping an eye on it while reindexing."

I believe this is due to the way that sql server tracks changes for certain operations while in bulk-logged recovery mode. For example, while the dbcc dbreindex is running and sql is in bulk-logged mode, sql server may just be keeping track of which extents have changed in the bulk changed map pages instead of sending modifications to the transaction log. Once this transaction(dbcc dbreindex) is finished, sql server just rolls all of the changed extents that occurred during the bulk operation into the next transaction log backup. This saves the log file but not the log backup.

Vincent-273794 SSC Enthusiast Points: 174 More actions · Answer 8

Don't you think, partitioning the backup would be help to reduce the backup time?

SSMfana SSC Eights! Points: 877 More actions · Answer 9

I run the above script and I got the error below what did I do wrong

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Msg 3206, Level 16, State 1, Line 28

No entry in sysdevices for backup device 'DD_Presents_Log'. Update sysdevices and rerun statement.

Msg 3013, Level 16, State 1, Line 28

BACKUP LOG is terminating abnormally.

jaimeortiz2005 Valued Member Points: 52 More actions · Answer 10

DBReindex is growing the transaction log because it is basically dropping and recreating the indexes you are building. You can use the SORT in TEMPDB option to do all the rebuilding operation in the TEMPDB. Do not forget to resize your tempdb to the largest clustered index size + your current allocation.

You can use the above method and also set your recovery model to bulk logged. This will help with your reindex job.

J.