December 14, 2010 at 11:48 am
I am running an reorganize index and it is causing the transaction log to fill up. There is a restricted growth size set. Should that be modified to be unrestricted? I would think it would be unrestricted but then I'm not sure if that is a good best practice.
December 14, 2010 at 11:59 am
To prevent this from happening, change the growth size to unrestricted. If you set the Growth size to restricted, then you need to monitor the log size growth continously or set up some alerts to get notified when the Log size hits 80% or 90% of its assigned fixed maximum size and then manually grow the file size.
To prevent these kind of scenarious, usually the log file max size is set to unrestricted. But in this case, the disk full percentage must be continously monitored or set up some alerts to get notified when the disk space hits 80% or 90% of its size and then do the needful.
Setting the Max growth size always depends on the environment and disk constraints. Setting it to unrestricted is not a bad practice.
Thank You,
Best Regards,
SQLBuddy
December 14, 2010 at 3:33 pm
If you are rebuilding/reorganising and maintaining your indexes regularly then you may be wise to set the size of your log right up front to allow for it. It will only grow again the next time your perform these tasks.
It is a good idea to have autogrow enabled and I really see no harm in having the file set to grow with no restrictions BUT this should only be used a fail back. You need to monitor your database file sizes and size your database accordingly
Hope this helps.
Gethyn Elliswww.gethynellis.com
December 14, 2010 at 8:54 pm
I just set this today for one of our databases:
1) Backup transaction log before indexes optimization
2) Move database to the bulk-logged recovery mode
3) Reorganize indexes
4) Move database back to full recovery mode
5) Perform full database backup
The log size shouldn't be unrestricted. Depending on database and indexes size it will grow differently. Try to set max size to the high number and allow autogrowth, for example set to 64 Gb if you have space (I usually set times 8 GB). Check after index optimization task how large log increased in size and reduce the max size to something bigger than that (if it's new size is 10GB set it to 16Gb).
December 14, 2010 at 9:24 pm
magasvs (12/14/2010)
I just set this today for one of our databases:1) Backup transaction log before indexes optimization
2) Move database to the bulk-logged recovery mode
3) Reorganize indexes
4) Move database back to full recovery mode
5) Perform full database backup
The log size shouldn't be unrestricted. Depending on database and indexes size it will grow differently. Try to set max size to the high number and allow autogrowth, for example set to 64 Gb if you have space (I usually set times 8 GB). Check after index optimization task how large log increased in size and reduce the max size to something bigger than that (if it's new size is 10GB set it to 16Gb).
As per me, this is the ideal steps to reorganize the indexes, I am also using the same steps.
Thanks
December 15, 2010 at 8:19 am
You all are the best. Most appreciated those you replied. I will implement what you have suggested.
December 16, 2010 at 9:26 am
Use DBCC LOGINFO to check your VLF's; if you are or were autogrowing in small increments, you may have a very large number of VLF's which will decrease performance.
December 16, 2010 at 9:58 am
Okay. I will run that SQL. Looking up what a VLF is right now. I don't recall that in any of my reading at the moment
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply