Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

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

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!


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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...