asked
.mdf and .ldf database setup
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.
You can either do this through TSQL with an ALTER DATABASE command your use the SSMS GUI and right click on the database and chose properties. Then you can look at the file sizes and change them accordingly. As pointed out by @Grant Fritchey, for model it is pretty well useless as you should be properly sizing the databases at the time they are created, however depending on who the database server is for, I like to set it to 1024 MB rather than 10 MB. I have come across way to many servers where model is 10 MB a database is set to auto grow by 1 MB and when I am asked to look into performance issues I find that a 60 GB database had those settings and grows on average 200 MB per day. See the problem there?
@sqladventure, about the only way you will get better performance with splitting a user db into multiple files is if you plan to spread the files across different raid groups (different spindles of disks). Creating multiple files on the same physical disks doesn't increase I/O. As for what criteria to use when deciding to split the data up, I try to use date. I try to "archive" data into yearly file groups. Working in the financial sector helps me with that tremendously since we have retention periods and are really just processing data. Once the data is written it doesn't tend to change.
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.
Thanks Grant. Another problem is the primary is at 350GB and I am thinking of creating secondary data files for better performance. But, I am not sure how I split up the files. BTW, I am reading your book, SQL Server 2008 Query Performance Tuning Distilled and learning a lot. I just want to give a giant THANK YOU for taking the time to write and sharing your knowledge.
You'll only get marginal improvements by adding files, if any, unless you also add drives. And even then you'll only see additional improvements if you're also adding drive controllers (unless we're talking SANs in which the rules change).
