locked
SQL Server 'Create Database' statement. How to inherit autogrowth settings? RRS feed

  • Question

  • Hi,


    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.


    Regards LAKSHMI NARAYANA REDDY.L http://social.msdn.microsoft.com/profile/lakshmi%20narayana%20reddy.l/

    Tuesday, November 27, 2012 1:11 PM

Answers

  • From a quick online search, it appears that this is a known issue that has existed for several releases.  The assumption is that SSMS uses SMO to retrieve the auto-growth properties from Model and populates the UI fields, but they are not supplied during scripting.  Obviously you can supply them yourself in your script, but it is not ideal.  There are also some scripts available to query the auto-growth size of Model to be used for your new CREATE DATABASE statement.

    http://dba.stackexchange.com/questions/21768/sql-server-create-database-statement-how-to-inherit-autogrowth-settings

    http://www.sqlservercentral.com/Forums/Topic1065073-391-1.aspx

    Strangely, I cannot find a connect bug on this specific issue.  If you file one, can you link it back here in case others stumble upon it?

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Tuesday, November 27, 2012 1:47 PM

All replies

  • From a quick online search, it appears that this is a known issue that has existed for several releases.  The assumption is that SSMS uses SMO to retrieve the auto-growth properties from Model and populates the UI fields, but they are not supplied during scripting.  Obviously you can supply them yourself in your script, but it is not ideal.  There are also some scripts available to query the auto-growth size of Model to be used for your new CREATE DATABASE statement.

    http://dba.stackexchange.com/questions/21768/sql-server-create-database-statement-how-to-inherit-autogrowth-settings

    http://www.sqlservercentral.com/Forums/Topic1065073-391-1.aspx

    Strangely, I cannot find a connect bug on this specific issue.  If you file one, can you link it back here in case others stumble upon it?

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Tuesday, November 27, 2012 1:47 PM
  • It seems by design.

    Refer Link : http://blogs.msdn.com/b/sqlserverfaq/archive/2008/12/10/did-you-know-when-we-create-a-database-using-create-database-database-name-database-properties-are-not-inherited-from-model-database.aspx 


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Tuesday, November 27, 2012 6:32 PM