January 4, 2008 at 7:50 am
When creating a new database what is the general rule of thumb when deciding the initial size of the Log and Data file size? Any info would greatly be appreciated.
Regards,
Doug
January 4, 2008 at 8:18 am
It would be prudent to spend some time and forecast future growth, account for partitions, file groups, etc. The idea is to make the data file(s) large enough up front so that the file(s) are contiguous. Avoid auto-grow whenever possible. Generally speaking, the log file should be 20-25% of the total MDF/NDF file size.
Tommy
Follow @sqlscribeJanuary 4, 2008 at 8:45 am
I agree, if you have no information on anticipated load, etc., about 25% of the initial data size would be a prudent number.
However, I'd make sure I had a more accurate picture of the anticipated log size by doing my due diligence with a good set of performance tests prior to going to production. You shouldn't be guessing at how things are going to perform or how much disk space you need.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 4, 2008 at 9:30 am
Thank you both for your input. Forecasting should be quite easy since I know how much data I'll be uploading from an older SQLBase DB. As far as avoiding auto growth, could you please explain why?
January 4, 2008 at 9:41 am
Personally, I use autogrow. But, I recognize that it is a crutch. I spend a lot of time & effort in setting up monitoring so that I know how much disk space I have, which databases are experiencing growth, etc. I also set realistic size limits so that while the files can auto-grow, they can only grow so much. Lastly, I have it grow by specific amounts, not as a percentage of the file.
The reason I do all this is because you never know when some unexpected process is going to come along and demand more space than you have currently allocated. I like to have the capability of letting the files grow automatically and then I'll catch it in the monitoring. Without it, I feel like you have to spend an inordinate amount of time setting file sizes.
YMMV
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply