June 27, 2012 at 6:27 pm
Hi,
I have been looking for the resource about this but couldn't find a satisfiable answer.
This is what I try to do: updating a 80 gb table in a controlled maintenance window. The columns I try to update are part of the clustered index. There are 5 more non-clustered indexes. The database is in the full recovery model. I would like to pre-allocate space to transaction log file so the automatic growth doesn't happen (or more importantly I don't run out of space). Is there a good way to calculate how much log this kind of transaction will generate on the log file? Something like "size of log needed = data + clustered index size (non leaf) + size of non-clustered indexes + other overhead (not sure what else get logged)?
Thanks,
Alan
June 27, 2012 at 11:39 pm
Take the total size of the clustered and non-clustered indexes (include non-leaf pages) and add 30%. I pulled that figure out of thin air, but it should be sufficient
though, you haven't mentioned what kind of updates you are doing, whether it will be one large transaction or multiple smaller ones, etc.
If you are going to be updating the clustering key, you should consider dropping the non-clustered indexes before the update, and rebuilding them afterwards.
June 28, 2012 at 9:36 pm
Thanks for the reply,
I am talking about updating one table at a time as whole transaction. The 30% seems to be about right as we did the update on the 80 gb table and the log grew to 120 gb (clustered + non-clustered). Since you said you pull the figure out of thin air, I assume this number is from your past exp on the similar operation?
Alan
June 28, 2012 at 10:03 pm
yes, but on a smaller scale (table < 1 gb).
good luck!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply