June 18, 2014 at 2:42 am
The initial size databases files get created with the size of the model database files.
What surprised me is it actually reserves that space, so if you wanted databases to start at 500MB as a minimum, for example, you have to waste 500MB for the model database.
Maybe this doesn't matter too much these days with big drives, but it seems a bit mad to me.
Do people change the model size, or just rely on databases being set up properly?
In my experience people seem to just accept the defaults until a problem occurs.
June 18, 2014 at 3:29 am
That is only if you accept the defaults. I normally leave model as it is and size the new databases appropriately in the create database script. If you are using the GUI to create a new database you can set different Initial Sizes for the new database. It is also a good time to change the default growth settings. I like to set my databases to the anticipated size after 12 months use, but you can't always get this information.
June 18, 2014 at 4:14 am
As part of our post-install checklist where I work we specify the size, autogrowth settings and recovery model of the Model database according to what generally works for us.
June 18, 2014 at 4:24 am
I usually do not change the model database size and preafer to keep it as default size. I never encounter any issue keeping model size as default.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
June 18, 2014 at 4:27 am
I suppose if you change model it's potentially affecting databases on the server that aren't yours.
I like the idea of having the customer sign off a checklist to confirm it's been done.
June 18, 2014 at 4:30 am
Changing model won't affect any of the existing databases on the server. It will only change the initial file sizes, recovery model, etc for any new databases you create after the change.
June 18, 2014 at 4:33 am
I have made very few changes to model over the years. Instead I just script out all the necessary changes I want to make to a database as I set it up for creation.
"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
June 19, 2014 at 2:40 am
I also prefer to size the database when I'm setting it up rather than using a "one size fits all" solution in model--not all databases are created equal, after all, and you don't want a database that gets maybe a few dozen rows of logging data inserted every day to be 500Mb!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply