?
Solved

Recommended initial size and autogrowth

Posted on 2009-06-28
8
Medium Priority
?
825 Views
Last Modified: 2013-11-05
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.
0
Comment
Question by:manduz_griffus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 200 total points
ID: 24733018
That entirely depends upon your business needs and the normal calculations are:

1. Check how much mdf and ldf file usage per day.
2. 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.
3. Calculate the LDF file usage per day and calculate your Full Backup durations. Calculate the LDF file space / usage per day and Make the Initial size as the space between your durations.

>> will I be able to change them on production time?

Yes... You can change the Autogrowth at any point of time.

>> Is it OK if I set the initial size to 4GB (max of express)?

Yes.. And if you dont use the 4GB max for huge no of days, then its non acceptable. But considering your small / average application, you can have it.

And for LDF you can set it to 1 GB or 2 GB Initial Size and 10-20% Autogrowth would help you out.
0
 

Author Comment

by:manduz_griffus
ID: 24733042
Hi rrjegan17,

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?
0
 
LVL 15

Assisted Solution

by:rob_farley
rob_farley earned 200 total points
ID: 24733058
Watch out for Autogrowth. It's definitely worth having on (since if a file fills up you'll start getting failures), but when Autogrowth occurs, the transaction that requires the growth will have to wait until the growth is finished.

So it's much nicer to monitor the size of the files and grow it yourself before Autogrow has the chance to kick in.

Another thing worth noting is that you shouldn't shrink your files. And I mean pretty much ever. Only do it if you've had accidental extraordinary growth, and even then, you'll want to rebuild all your indexes afterwards. Shrinking files is notoriously bad for introducing fragmentation, and it will probably just cause Autogrowth to be required straight afterwards.

Also, log file growth is a special case. When a log file grows, the disk needs to be zeroed out, so it should be avoided whenver possible. But you should also make sure you understand the difference between Simple and Full recovery models. In Simple, the log file is truncated frequently. In Full, the log file is only truncated when a backup happens. Truncation means that the file will empty out, so that other data can be inserted without growing the file. So if you're in Full, do lots of transaction log backups. If you don't need the information in those backups (for point-in-time restore), you should probably be in the Simple recovery model. Many people end up running in Full by default, and have massive log files.

Hope this helps...

Rob
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 15

Expert Comment

by:rob_farley
ID: 24733062
You can grow your file yourself at any time. I think that's what he means by "Initial size".

Rob
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24733257
>> 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.
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24733289
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
0
 

Author Closing Comment

by:manduz_griffus
ID: 31597754
Thank you very much guys! Very useful information for me!
0
 

Author Comment

by:manduz_griffus
ID: 24733505
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!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question