Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

autogrowth settings not inherited from model Expand / Collapse
Author
Message
Posted Wednesday, February 16, 2011 9:07 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, December 15, 2014 9:36 AM
Points: 652, Visits: 1,901
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: )
Post #1065073
Posted Wednesday, February 16, 2011 10:34 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 15, 2012 4:11 AM
Points: 369, Visits: 2,661
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)
Post #1065128
Posted Wednesday, February 16, 2011 11:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
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
Post #1065175
Posted Thursday, February 17, 2011 12:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 4:05 AM
Points: 34, Visits: 95
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.
Post #1065468
Posted Friday, May 27, 2011 6:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 24, 2011 1:55 AM
Points: 139, Visits: 43
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
Post #1116184
Posted Tuesday, July 31, 2012 9:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 5, 2012 9:25 AM
Points: 1, Visits: 8
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'?
Post #1337969
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse