Recently I was reviewing one of our supported instances and I saw a bunch of databases with 1MB autogrowth on the data file.  Most were quite new, and some had grown to quite a large size.  Paul Randal deals with ‘the default’ settings for autogrowth in this blog post, and says 1MB is not a good option.  No surprise there as for a highly transactional system you are going to have the data file growing all the time.  So I want to change it, but I also want to understand where it came from.

This is a managed server, so I know the model database is not going to have 1MB autogrowth set up, but just to be sure I check and confirm.  So if it’s not getting the default setting from the model database, where is it coming from?  Time to test. So when I use the Management Studio GUI it pulls settings from the model database as I’d expect.  And when I script those out using the ‘SCRIPT’ option in the create database wizard it faithfully transfers all the settings.  But what about if I just use ‘create database’ by itself.

CREATE DATABASE MODELTEST

Sure enough this grabs the default setting from when you install SQL Server – 1MB, and not the updated value in the model database.  I also noted it grabs the default data file size, but not the default log file size.  This was news to me, and led me to wonder what other settings were not being pulled from model.  So I changed a bunch of options in model on the options tab to test.

All of the other options I tested transferred properly.  Or at least as I anticipated.  So….nice one SQL Server…..you will transfer autoshrink being enabled but not transfer the log startup size or data growth settings.