manduz_griffus
asked on
Recommended initial size and autogrowth
Hi,
I'm beginning SQL Server 2008 and need some tips on what values to set to initial size and autogrowth. My situtation is this: DB gets CRUD frequently everyday. I probably can estimate the initial growth of the data file but what should I put in autogrowth. And what is better, by MB of percentage. Also, the same questions on LDF files. I think log files will grow faster so what values should I set. And BTW, im using SQL Server Express as of the moment. Is it OK if I set the initial size to 4GB (max of express)? And if I ever choose wrong values (or udesirable values), will I be able to change them on production time?
Any thoughts regarding this will be very much appreciated. Thanks.
I'm beginning SQL Server 2008 and need some tips on what values to set to initial size and autogrowth. My situtation is this: DB gets CRUD frequently everyday. I probably can estimate the initial growth of the data file but what should I put in autogrowth. And what is better, by MB of percentage. Also, the same questions on LDF files. I think log files will grow faster so what values should I set. And BTW, im using SQL Server Express as of the moment. Is it OK if I set the initial size to 4GB (max of express)? And if I ever choose wrong values (or udesirable values), will I be able to change them on production time?
Any thoughts regarding this will be very much appreciated. Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can grow your file yourself at any time. I think that's what he means by "Initial size".
Rob
Rob
>> And if you dont use the 4GB max for huge no of days, then its non acceptable
Let me explain this more clear.
If you have a DB with usage of 1MB per day and you set the Initial File Size as 4 GB, then it would take nearly an year for it to reach the file size of 4 GB.
Hence in this scenario, you can set your Initial File size as 100 MB (for the example I gave) and your Autogrowth to 10 - 20 % which would help you out.
If you specify 4 GB file space, that space would be allocated for SQL Server and it would cause high level of Fragmentation.
And one more concept:
When you specify the autogrowth property to say 10 MB, a virtual data / Log file would be added in your MDF / LDF file and hence if you have autogrowth set to less, then it would create lot of Virtual Log files and hence the fragmentation increases.
Hope this clarifies you out on what I meant over there.
And Make sure you have appropriate maintenance tasks to take Log File Backups and Shrinking the Log File alone.
Dont shrink the MDF file as it would not do anything better to your database except increasing the fragmentation.
Let me explain this more clear.
If you have a DB with usage of 1MB per day and you set the Initial File Size as 4 GB, then it would take nearly an year for it to reach the file size of 4 GB.
Hence in this scenario, you can set your Initial File size as 100 MB (for the example I gave) and your Autogrowth to 10 - 20 % which would help you out.
If you specify 4 GB file space, that space would be allocated for SQL Server and it would cause high level of Fragmentation.
And one more concept:
When you specify the autogrowth property to say 10 MB, a virtual data / Log file would be added in your MDF / LDF file and hence if you have autogrowth set to less, then it would create lot of Virtual Log files and hence the fragmentation increases.
Hope this clarifies you out on what I meant over there.
And Make sure you have appropriate maintenance tasks to take Log File Backups and Shrinking the Log File alone.
Dont shrink the MDF file as it would not do anything better to your database except increasing the fragmentation.
No, don't shrink the log file. Shrinking a log file only causes it to grow more often. It's much better to have a larger, emptier log file that never has to grow.
The only type of fragmentation you could have caused by having a larger, emptier file is from the Operating System perspective - but if you're worried about that, then do a defrag before you create the 4GB MDF file.
Autogrowth should be left on, but you should hope that it's never used.
Rob
The only type of fragmentation you could have caused by having a larger, emptier file is from the Operating System perspective - but if you're worried about that, then do a defrag before you create the 4GB MDF file.
Autogrowth should be left on, but you should hope that it's never used.
Rob
ASKER
Thank you very much guys! Very useful information for me!
ASKER
Thanks rrjegan17 and rob. Your suggestions really are helpful as a guideline for someone like me beginning SQL Server 2008. Your answers are greatly appreciated!
ASKER
Thanks for replying.
In your no 2., you wrote:
"Calculate the MDF file usage for a week and set that as Initial Size and make Autogrowth to happen once per day.. ie., A minimum of 10 - 20% autogrowth will help you out". So I will do this calculation using sample data during development? Initial size can't be change (I think) during production, or am I wrong.
And also, what did you mean when you wrote
"And if you dont use the 4GB max for huge no of days, then its non acceptable".
Your other answers looks very acceptable, I'll try those out. And also, is the 4GB limit of SQL Express only for the Data file?