May 5, 2009 at 3:48 pm
As mentioned in subject, Initial size of Log file is 65 GB and growth is set to 30%. The database normally will take less load but all the data gets refreshed from other server over night.
and Only other operation is Re-indexing, a weekly job. I am planning to change this log to grow @ 1 GB instead of percentages.
And also will regular log backup jobs bring back my space from logs and how about bringing back my data file space after re-index?
Pls suggest. For some reason, i cannot go with manual increase in space.
Thanks,
Sudhie.
May 5, 2009 at 4:18 pm
Is this a reporting database?
Does it gets fully refresh overnight?
Is your backup strategy setup on full recovery model?
If YES, YES, YES... set your backup strategy to simple mode and let tlog truncate after each transaction.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 5, 2009 at 4:25 pm
From what you're saying it sounds like the data is only changed by the nightly refresh. If that is true I would set the database to Simple recovery and institute a full backup of the database after the load is complete (if that's not already in place) and also after the weekly reindex.
The required size for the log file of a database in Simple recovery is dependent on how much data is changed by the largest transaction. Assuming that you're nightly data refresh is incremental then the largest transaction is going to be the rebuild of your largest index. Assuming your largest table has a clustered index then the reindex of that table is likely to be the largest transaction. If that is the case set the log file to around 120% of the size of that table. I'd change the growth on the log file to be 10GB rather than a percentage.
As you can tell from my answer there's a lot of assumptions I've made, but what I've described are the "guidelines" I would use for such a situation.
May 5, 2009 at 4:25 pm
It gets refreshed for the first week and rest of the month, it will have calculated data and less transactions coming in. I am fine to suggest client on changing the recovery model, but again depends.
Paul - Will 1 GB growth create any performance issue?
May 5, 2009 at 4:31 pm
Glenn Dorling (5/5/2009)
The required size for the log file of a database in Simple recovery is dependent on how much data is changed by the largest transaction. Assuming that you're nightly data refresh is incremental then the largest transaction is going to be the rebuild of your largest index. Assuming your largest table has a clustered index then the reindex of that table is likely to be the largest transaction. If that is the case set the log file to around 120% of the size of that table. I'd change the growth on the log file to be 10GB rather than a percentage.
Glenn - Lets go with assumption of still having Full Recovery Model, what will be your guide lines? Database size is around 150 GB.
May 5, 2009 at 4:37 pm
If you have the db at 65GB, what percentage is used, and what is the growth rate?
For the re-indexing, you need space, but that depends on your clustered indexes, which are likely the largest items. You need 2.5x the space of the largest one to be safe.
For the loads, what log space is needed? there's no way to tell other than run some loads and see what the log backups are like afterwards. That will tell you if you need more log space. The log is sized to handle your peak transactional load between backups.
However if this is only changed by a load process, I'd lean towards simple mode, as Paul mentioned, and then back it up after the load.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 5, 2009 at 5:08 pm
If the database is in Full recovery then the log file size is a bit harder to predict because transactions will remain in the log file until it is flushed, either by a log backup or by a manual truncate (which would break the log cycle anyway so shouldn't ever happen in normal circumstances).
One thing I would do is to review whether the database really needs to be in Full recovery. If the data is only changed by the nightly refresh then having it in Full recovery is pointless.
If it is going to remain in Full recovery mode the very first thing to do would be to institute a regular log backup if there's not one already.
For a database in Full recovery with log backups at least every 30 minutes my initial estimate for the log file size would be 150% of the largest transaction, which as before I would expect to be the reindex of the largest table. If the log backup was being done only hourly I would probably make the factor 200%. Because I like round numbers I'd round the resultant figure up to the nearest 10GB and I'd set the growth factor to 10GB. I wouldn't be surprised if it expanded once or twice the first time the tables were reindexed (depends on how the log backup and rebuilds of individual indices line up) but after that the log file growth should very closely parallel the data growth.
May 6, 2009 at 1:54 pm
Client confirmed today, this is not completely reporting and want full recovery model. Thanks a lot for your inputs, i may got for 10 GB increase and will enable the log backup (all in dev) and monitor things.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply