January 10, 2012 at 5:46 am
I am not a DBA but get into it on a periodically basis.
We have db size of 50G with 25.3G free or 50%. Breakdown is as follows: Data 42G allocated with 17G free or 40%. Log 8.3G with 8.2 free or 99%. DB backup every day. Trans log backup everyday from 6 am to 11 pm. Only indexes with pages > 10 and fragmentation > 10 are reindexed once per week(this should probably be changed). The current(legacy) auto-growth is set at 3% for both data and log. We do not expect rapid db growth but can setup scripts to monitor. Given these conditions should auto-growth be set to 0 for both data and log? What is the most reasonable page count and fragmentation level to do DBCC DBREINDEX for the selected table\index?
January 10, 2012 at 6:05 am
Probably best to monitor file size proactively but set autogrowth just in case. The fragmentation levels I've seen used most often are to reorganise indexes above 5% and rebuild above 30%. Don't really know about page count - I think maybe 1000 rows is reasonable. The exact figure shouldn't matter that much because it won't take very long at all to rebuild indexes on such small tables.
January 10, 2012 at 6:12 am
Thanks John. Given the free space in the files should 1% auto be ample? Isn't this forcing the free space in each file to be used? Can you suggest proper way to monitor the file sizes before alter db command is required to increase the sizes?
January 10, 2012 at 6:19 am
Tim Jacobs-356724 (1/10/2012)
Thanks John. Given the free space in the files should 1% auto be ample? Isn't this forcing the free space in each file to be used? Can you suggest proper way to monitor the file sizes before alter db command is required to increase the sizes?
Do you have dbs with more than 1 data file?
1% is too low, you should set this to 0.5-2 GB (whatever your system can grow without taking more than a few seconds).
But first you must monitor, autogrow should be your final, final last failsafe.
Edit, can you confirm you are on sql 2000?
January 10, 2012 at 6:22 am
Free space isn't relevant since autogrow is what happens when the free space runs out. It doesn't force any space to be used. 1% is probably rather low, but in any case it's usually best to use a fixed number of MB instead of a percentage (maybe 500 to 1000 given the size of your database). sp_spaceused is a good place to start for monitoring. There are more sophisticated techniques, which you should be able to read about on the internet.
John
January 10, 2012 at 6:36 am
I just need to understand one point. If I have free space = 99% or 40% doesn't that free space have to be consumed(or show as being reduced) before autogrowth kicks in?
January 10, 2012 at 6:38 am
Yes.
January 10, 2012 at 6:40 am
Yes, that's right. But of course once it's consumed or reduced, it's closer to 0% than to 40 or 99.
John
January 10, 2012 at 6:42 am
Thanks Ninja. Yes it's SQL 2000. So everyone would suggest that I change from percent to file growth in megabytes(500-2000_ but autogrowth would still be on.
January 10, 2012 at 6:44 am
Tim Jacobs-356724 (1/10/2012)
Thanks Ninja. Yes it's SQL 2000. So everyone would suggest that I change from percent to file growth in megabytes(500-2000_ but autogrowth would still be on.
Yes.
You need to test to make sure it can happen within 1-2 seconds so that the users don't wait on it.
Then search the internet for monitoring scripts. I have one but it's for 2K5+.
January 10, 2012 at 6:45 am
So in effect then data and log files are not growing because they have so much free space and the 3% growht is not revelant at the time.
January 10, 2012 at 6:47 am
Tim Jacobs-356724 (1/10/2012)
So in effect then data and log files are not growing because they have so much free space and the 3% growht is not revelant at the time.
Yes.
Why do you want them to grow so bad?? 😉
January 10, 2012 at 7:03 am
Ninja, I don't want them to grow bad just trying to understand if the 3% is even revelant at this time given the free space. Some literature suggest never using autogrowth but monitor and adjust accordingly. I do appreciate the guidance from you and John.
January 10, 2012 at 7:08 am
Tim Jacobs-356724 (1/10/2012)
Ninja, I don't want them to grow bad just trying to understand if the 3% is even revelant at this time given the free space. Some literature suggest never using autogrowth but monitor and adjust accordingly. I do appreciate the guidance from you and John.
Never RELY on autogrow. It's an insurrance against unforseen data spikes or other problems.
You should set it to 500 to 2000 mb and move on to your next task. That way the db(s) won't break (running out of space does that, no more transactions can happen, but read is ok).
January 10, 2012 at 7:21 am
Thanks Ninja. So as you and John suggested the final settings on SQL 2000
autogrowth=Yes
In Megabytes 500-2000
Unrestricted file growth = Yes
monitor using sp_spaceused or via scipt and keep doing what we're doing with the backups.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply