Post reply

autogrowth settings not inherited from model

  • February 16, 2011 at 9:07 am

    #233785

    When I issue a 'CREATE DATABASE mydb' statement, I would expect the database settings to come from the system database model. However I observe that the autogrowth settings do not. e.g. if I change the autogrowth settings in model, these new settings are not reflected in databases created with CREATE DATABASE. If in SSMS, however, I right-click databases and choose New Database, the settings are as expected.

    I'd like to know if any of you can confirm this behaviour (i.e. that it's not just me!), and ideally explain or refer me to documentation relating to this.

    Thanks,

    David McKinney.

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Developer Edition on Windows NT 6.1 <X86> (Build 7600: )

  • February 16, 2011 at 10:34 am

    #1287789

    I'm getting the same (SQL 2008), with autogrowth settings and initial file size defaulting to 3MB, by 1MB (data), , 1MB, by 10% (log) when the simple statement CREATE DATABASE MyDB is used.

    I tried it on SQL2005, same thing.

    ------------------------------------------------------------------------
    Bite-sized fiction (with added teeth) [/url]

  • February 16, 2011 at 11:42 am

    #1287828

    I could not reproduce that issue. Since you are on SQL Server 2008 RTM, check if applying SP2 helps in resolving this.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • February 17, 2011 at 12:56 am

    #1288064

    Well, I can reproduce the issue (SQL 2008 R2 x64 - 10.50.1746). If you create the database using the GUI it takes over the model settings for autogrowth. If you do it with a SQL script "create database mydb" it does not but reverts to the original basic settings.

  • May 27, 2011 at 6:39 am

    #1330933

    Isn’t there some global setting for model database which can configure the auto grow attribute of any new database created to allow for unrestricted access?

    Even though the model database itself has auto grow configured to unrestricted, this particular property is not inherited by the new databases created. The default restricted growth size is substantial i.e. 2,097,152 MB. I am wondering is this was done on purpose to keep the log file size in check or something by Microsoft.

    What I hear I forget, what I see I remember, what I do I understand

  • July 31, 2012 at 9:10 am

    #1519780

    I can reproduce the problem in both SQL 2008 R2 and SQL 2005 SP3

    I have read in several places that default settings for a new db should be based on model, but it appears this only happens by clicking new database in SQL Management studio UI and not from a script e.g. CREATE DATABASE [MyDb].

    http://msdn.microsoft.com/en-us/library/ms186388(v=sql.105).aspx

    http://stackoverflow.com/questions/8828557/possible-to-configure-database-autogrowth-settings-at-the-instance-level

    Please could anyone point me to a Microsoft knowledge base article that documents whether this should actually work, or is it 'by design'?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply