April 1, 2014 at 5:48 am
I'm just looking for some guidelines on how everyone determines what to use as their auto growth settings for system databases and user databases.
April 1, 2014 at 6:27 am
The one recommendation I can make is that the defaults don't really work well as your databases get bigger. Growing by percentages gets worse as the databases get larger but growing by really small amounts leads to massive storage issues. So, the answer is to grow by a set amount, but a larger set amount, but not so large that you get massive waits while the growth occurs. Exact numbers are up to you.
"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
April 1, 2014 at 6:27 am
Usually I do not change default setting of system databases.
About user database based on the db size and data growth you can set auto growth option but I suggest instead of % you can use growth in MB like 250, 500, 1000, 2000, 5000 etc.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 1, 2014 at 6:52 am
As the others have said, there's not really a great way to determine this off the bat. Make sure you create decent size "initial size" for the files. Make sure the autogrowth setting is large enough so that it doesn't grow multiple times a day. So percentages and if you see something like 1 MB is bad. For new DB's with unknown growth amounts, I tend to just set autogrowths of both the log and data files to around 1000 MB and sometimes higher depending on the expected use. Don't be afraid to allocate more space to the initial size if you have it. I like to then set up an alert that tells me when the log has risen above 80% used so that if my expectations were completely wrong I'll be notified.
April 1, 2014 at 6:53 am
for all databases except master,model,msdb, the databases should be presized so autogrowths are avoided, this is very important on tempdb.
Having said that, for all databases except master and model (so including msdb)
data files - change to a MB value, not a percentage. the value depends on the size of your database and amount of free space left on the drive. Normal values are in the range of 100MB - 1GB. Use instant file initialisation so the growths happen quickly with least effect on application.
log files - bear in mind you want to keep well sized vlfs and keep the number of VLFs down. Up to 64MB growth chunks 4 VLFs are created, between 64mb and 1024MB 8 are created, above that 16 vlfs are created. So depending on the base size of the log growths should be 64MB or multiples of that up to 512 MB, then 1024MB with a maximum of 8000MB (note not 8GB due to a bug which as far as i know still exists). Logs cannot use instant file initialisation so bear that in mind.
Change the defaults on model purely so any new databases created pick up these values. Databases created via TSQL do NOT pick up the log default.
edit - cannot spell TSQL!
---------------------------------------------------------------------
April 1, 2014 at 7:32 am
All great points mentioned, so I'll contribute one last item: If you use multiple data files for TempDB, autogrowth should be avoided. As others have mentioned, it's best to size accordingly
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 2, 2014 at 6:51 am
I will add that you should not rely on "autogrowth" to manage your database growing/sizing. "autogrowth" should be used as a last resort for growing your database so the database doesn't shut down. Why would you want the database growing in the middle of the day which definitely can cause performance issues?
Run reports weekly/daily that provide you the current database size and how full the files are. You will then see the rate of growth and can project when the file will fill, or be near full.
When you get to <threshold>, say 90% full, then take the time to manually grow your file during "off hours", or minimal activity. At this point you can grow by a large sizeable chunk that is more meaningful and useful than "autogrow".
Steve
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply