April 12, 2007 at 7:48 am
HI,
In SQL Server 2000, I run a maintenance plan Optimization job (reindex) once a week. Is there a way I can tell how large the tranlog needs to be to accomodate the reindex? Using SQL DM I can see that total index size is just over 20GB - tran log is allocated 20GB and so far the job has not failed. I just increased allocated size for the database to 100GB from 50GB. The reason for my concern is, at 50GB allocated for the db, a 10GB tranlog would fill during reindex, even with a log backup just prior, so we bumped the tranlog to 20GB. I am assuming that in time, the 20GB won't be enough but would like to base a change on something other than a newbie guess! I run a full backup daily, differentials every 4 hours and tranlog backs up hourly.
Any information will be greatly appreciated!
Thanks,
Kathy Cowens
April 12, 2007 at 9:01 am
How long is a piece of string??
I tried working this out a while back with no success! You will also notice that the more fragmented an index is the more logging will be required (as a collague found out when he reindexed one of our main tables this past weekend!)
Your best plan of attack is to take note of the number of rows and level of fragmentation. As you start capturing this data, you should hopefully see some trends and be able to start estimating disk space requirements, files sizes and possibly reindex times.
Clive
April 12, 2007 at 9:02 am
It will be mostly a trial and error. I've seen 1.5 largest table or even 2x largest table for the data movement. For the log, depends on rows and will change as your data grows.
The best thing to do is monitor it with system monitor, grab the largest size over the job time and go a little larger. Might need to adjust it every quarter/6 months if data is growing.
April 12, 2007 at 9:05 am
Could you change the database recovery mode to SIMPLE before running reindex?
Of course, you need to change it back to FULL after completion.
April 12, 2007 at 9:42 am
Thank you all for the prompt responses - now I have something to go on. The help is very much appreciated!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply