What is the best practice/guide to set .mdf & .ldf with file growth and maximum file size for a database? I have a database that is set with the below settings and I am thinking of changing the .ldf file growth and the restricted file growth. But, I wanted to make sure I am setting this up correctly and looking for a guide on how to calculate this kind of db setup and/or new installs. database.mdf -> Initial Size (MB) 360000, Authgrowth is enabled, File Growth: In Percent: 10, Maximum File Size: Unrestricted File Growth database.ldf -> Inital szie(MB) 85000, Autogrowth is enabled, file growth in Percent:20, maximum file size: Restricted File Growth(MB): 2,000,000. There is no .ndf. Please help. Thank you.
First off the only way to project future growth of a system is to know previous growth patterns. You have to have a bit of history of the data you are working with. Second I don't like percentage growth period. I use a fixed size for growth and do have auto grow turned on. I would rather take the performance hit with my tlog or data file growing during production hours rather that the system halting altogether. If the file runs out of space it can't write any additional transactions if auto grow is turned off. Also while the files are expanding transactions are halted. At least if the file is having to expand during peek production business will resume pretty darn quick. Rather than a call having to be made to a DBA and them manually expand the size of the file. I usually set my auto growth size to 1 GB or so. My servers can expand the file to that size in seconds. I try to keep tabs on my databases by monitoring the amount of free space within the file and when it gets close I schedule the expansion during non peek hours. This has worked pretty well so far. Initial size comes into play with Model and tempdb if you have resized tempdb. I hope this has answered your question.
I wouldn't say this is a seperate answer, rather an addition to @TRAD excellent answer. The reason there is no mathematical, step-by-step process of setting up databases is because it really depends on the needs of your application and the needs of your business. Initial sizes on the user databases depends on how much data you expect to put in over the short term, say, six months. Initial sizes on tempdb are harder to gauge, but hopefully you've been testing your application prior to putting it into production so you have some idea of how tempdb has been affected in your test environment. Use that as a basis. Model size is completely up to you. It only drives the default values of the database files and you can override those when you create the database, so it's largely meaningless. Plus, if you make it really big, because all your databases are going to be really big, your sacrificing all that storage space for a very minor amount of convenience. I'd leave it completely alone.