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.

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.

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!

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, SSMS, System Databases, System Functions and Stored Procedures, T-SQL Tagged: code language, language sql, microsoft sql server, sql statements, SSMS, system databases, system functions, T-SQL