January 13, 2011 at 3:19 pm
Hi, I have this question regarding Transaction Log Backup file growth. In my production SQL 2005 server, I am runing ‘DBCC INDEXDEFRAG’ twice a week to maintain health of the indexes. Here I have noticed that the size of the very first T-Log backup after ‘Indexdefrag’ is significantly higher in size. Could someone let me know, the reason for the same and whether this can be minimized?
Database size = 50 GB
Full Backup Size (daily) = 35 GB
T-log Backup Size (every 1 hour) = 90 MB to 100 MB
T-log Backup Size (After INDEXDEFRAG execution) = 20 GB
Many thanks!
January 13, 2011 at 3:41 pm
First, you might look at this script for a more efficient index rebuild: http://sqlfool.com/2009/06/index-defrag-script-v30/
An index rebuild is a logged operation. So rebuilding the indexes will result in lots of log records. Price of doing business, just plan for it, and leave the log at the largest size that you need for your operations.
January 13, 2011 at 3:43 pm
This is a logged operation - from Books Online;
"The defragmentation is always fully logged, regardless of the database recovery model setting. For more information, see ALTER DATABASE (Transact-SQL). The defragmentation of a very fragmented index can generate more log than a fully logged index creation. However, the defragmentation is performed as a series of short transactions, so a large log is unnecessary if log backups are taken frequently or if the recovery model setting is SIMPLE."
IF you set the recovery model to SIMPLE prior to running the defrag you will have to take a FULL backup prior to running log backups again though. So, don't consider that an option for keeping the log smaller unless you are prepared to handle the backup changes as well.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 13, 2011 at 4:56 pm
Thanks Guys.
The server is belonging to a consumer website and remains busy all the time. So, I am having a challenge in maintaining the index all the time. So, it seems I do not have any alternative but to remain with the large log backup size post index defrag.
My second question is that, during the time index defrag is running if the T-Log backup job kicks off then I am seeing lots of blockings. Almost slowing down the entire application. Do you have any idea how to tackle this?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply