Post reply

MODEL database filesizes not being used

  • April 24, 2013 at 4:32 am

    #277009

    Has anyone else noticed that the MODEL database filesizes and autogrowth settings aren't applied to a user database if you use the basic CREATE DATABASE statement?

    e.g. Set MODEL to have the following:

    datafile: Initial size = 100Mb, autogrowth = 100Mb, Unrestricted;

    LogfileL Initial size = 10 Mb, autogrowth = 10%, Unrestricted

    Then run:

    CREATE DATABASE fred

    GO

    Check the file properties for the database Fred.

    On my system (2008R2 SP1 CU1) I get:

    Datafile: Initial Size = 100Mb Autogrowth = 1 Mb (!), Unrestricted

    Logfile: Initial Size = 25Mb Autogrowth = 10%, Restricted

    Not the end of the world, of course, but potentially some awkward gotchas if you're not paying attention, and also irritating.

  • April 24, 2013 at 5:06 am

    #1609407

    good spot.

    If you look a the create database statement in msdn it states:

    'When a CREATE DATABASE database_name statement is specified without additional size parameters, the primary data file is made the same size as the primary file in the model database.'

    so all it is promising is the size of the primary file will be taken from model. So the lesson is always specify your file sizes and growth factors.

    If you create the database vis the GUI however all model defaults are used. This is because behind the scenes the create database is performed in the context of the model database and all values for the new database can therefore be extracted from metadata details held about model.

    ---------------------------------------------------------------------

  • April 24, 2013 at 5:14 am

    #1609409

    I just tested it and I see the same thing on 2008 R2 SP2 (10.50.4000.0):

    When using basic CREATE DATABASE in TSQL with no parameters, your new database gets a primary data file which is the same size as the one for model DB, but file autogrowth settings are NOT copied. So, yeah, bottom line is: Always make sure to use the settings you really want.

    Kind regards,

    Vegard Hagen
    Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
    @vegard_hagen on Twitter
    Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

    It is better to light a candle than to curse the darkness. (Chinese proverb)

Viewing 3 posts - 1 through 2 (of 2 total)

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