I'm moving a database from MySQL to SQLServer. When creating the database, what should I set the initial size to? I know the size of the imported database will be about 130MB but will grow. Should 130MB be the initial size or should I just take the default of 2MB?
+1
A:
You should make it the correct size to fit your data, you will get a performance hit whenever the file needs to grow.
It depends how fast it would grow, I would say 150MB with 10% Autogrowth.
There is advice on the MSDN that is worth a read.
Chris Diver
2010-07-09 19:58:34
{{Shudder}} Don't *ever* use percentages for auto growth. That's just asking for trouble. If the database is small, it could need to auto-grow bunches of times in a short period killing performance. If the database gets large, a 10% auto-grow may mean a huge file growth, killing performance. Databases don't grow in off hours either - by definition they do it when they're being hit. Pick something reasonable for your expected data load - say 50-200MB. DBAs everywhere will rest a little easier knowing there's one fewer DB with an auto-grow percentage.
mattmc3
2010-07-09 22:46:26
You can flip your argument on it's head, if the database is small and it has a fixed 50MB growth then you are growing it larger than necessary. If his database gets large say 100GB and he has set 50MB autogrowth then it will be constantly growing, again a performance hit. The reality is that it should be assessed on a case by case basis, I don't see how you can recommend a fixed size without knowing the usage, hence the percentage and a link to a more detailed recommendation. ...
Chris Diver
2010-07-10 11:40:07
...Auto-growth is a safety measure and not a way to increase the size of your files, due to the unpredictable nature of when this will occur. Database do grow in off hours if you manage them correctly, you should use a job with `sp_spaceused` or another method to check the size of your database files and inform you before it autogrowth occurs, allowing you to do it manually at a convenient time.
Chris Diver
2010-07-10 11:40:48
+1
A:
Set it to at least your current size, probably with a decent buffer for immediate growth during the migration. Depending on growth rate I would do something like:
Initial: 150MB (or 200MB if size isnt an issue)
Autogrowth: yes
Autogrowth Size: anywhere from 5MB to 25MB (depending on your growth expectations)
Taylor
2010-07-09 19:58:52
A:
200 MB with a 50 MB auto-grow is the right solution. Remember to use separate drives for tempdb, and if possible put your logs on different disks from your data too if you need better performance. Also, remember that your data may be 130 MB, but you need to think of your indexes too and how much space they'll consume. Also, even if you have a 200MB data file, your backups files will be much smaller, which is often where the real space concern is when talking about small DBs like this.
mattmc3
2010-07-09 22:51:52