The default autogrowth settings do NOT come from the Model database.

5

April 28, 2014 by Kenneth Fisher

I recently saw a question about How to inherit autogrowth settings. They commented that while the new database GUI seemed to inherit the autogrowth settings from the model DB the CREATE DATABASE command did not. Now my initial reaction was “Of course the autogrowth settings are pulled from model. All of the new database settings are pulled from model!” I then ran some tests in order to get images for an answer and discovered that I was wrong.

I know shocking isn’t it?

Let’s start with the GUI which in theory does use model as a pattern for the autogrowth settings.

AutoGrow1

If however you hit the script button you will get the following code:

CREATE DATABASE [NewDBTest]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'NewDBTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\NewDBTest.mdf' , 
SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'NewDBTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\NewDBTest_log.ldf' , 
SIZE = 1024KB , FILEGROWTH = 10%)
GO

Which you will notice has the autogrowth settings explicitly defined. Now it does use the values from the model database to construct the command. Unfortunately that isn’t what we are looking for.

As a test I changed the autogrowth settings on each of the system databases (just for the heck of it). master=11%, msdb=12%, model=13% and tempdb=14%.

Then I run the following command.

CREATE DATABASE [NewDBTest]

And then when we look at the settings we see they are still the original default for model, not the altered one. And in fact it is not the altered setting for any of the other system databases.

AutoGrow2

To be fair it does pick up the initial size of the data file, but the authgrowth/max size settings are still the initial install default. Now personally this looks like a bug to me and in fact here is a connect link on the subject although it has already been closed with “as design” as the reason and the unhelpful response that since you can perform a work around they won’t be fixing it. If anyone knows of a connect entry on the subject that I missed please add it to the comments so I can up vote it!

5 thoughts on “The default autogrowth settings do NOT come from the Model database.

  1. […] is an interesting post from Kenneth Fisher were he discusses the settings and how it is a known issue that Microsoft is […]

  2. […] as it happens, makes it a great T-SQL Tuesday subject. You could easily talk about the default database growth/size settings or server settings such as instant file initialization and lock pages in memory. Not to mention all […]

  3. […] The default file sizes didn’t used to come from the Model database. This is no longer true! […]

  4. Johnson says:

    Fantastic post! This is so chock full of users information and the resources you provided was helpful to me.There i found one more informative article. You may also have a look:
    http://www.sqlmvp.org/database-autogrowth-in-sql-server/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,646 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: