Database Size

  • abmore

    Hall of Fame

    Points: 3628

    Comments posted to this topic are about the item Database Size

  • This was removed by the editor as SPAM

  • jshailendra

    SSCrazy

    Points: 2260

    I agree with Stewartc, In 2008 intial size for mdf is 3 MB and for ldf its 1 MB, so initial size of DB would be 4 MB and not 3 MB.....We want our points back 😀

  • Arjun SreeVastsva

    SSCertifiable

    Points: 7135

    In Sql Server 2000 New Database Size is 2.00 MB

    and unallocated spcae is o.43 mb...so the in the question you specify the versions 2000,2005,2008 Can u give right marks to 2 MB ALSO....

    In 2008 intial starting size for mdf is 3 MB and for ldf its 1 MB, so initial size of DB would be 4 MB so it is..?

    FOR PAST 2 DAYS QUESTIONS WERE DOEN'T UNDERSTANDING ?

    WHAT HAPPEND TO SQL CENTRAL SERVER ?

  • b0b555

    Mr or Mrs. 500

    Points: 541

    This statement doesn't make sense to me:

    "The default size for an mdf is 2MB and 1MB for an ldf, based on the model database".

    It can't default to 2mb/1mb AND default to the values in the model db. How can it default to 2 different things?? It has to be one or the other, surely...

    If it is based on the model database, then the default size is whatever is specified in the model db, in my case 100mb/10mb.

  • SQLRNNR

    SSC Guru

    Points: 281243

    b0b555 (1/20/2010)


    This statement doesn't make sense to me:

    "The default size for an mdf is 2MB and 1MB for an ldf, based on the model database".

    It can't default to 2mb/1mb AND default to the values in the model db. How can it default to 2 different things?? It has to be one or the other, surely...

    If it is based on the model database, then the default size is whatever is specified in the model db, in my case 100mb/10mb.

    If unaltered, then the default settings would be the 2 and 1 in the model database. But this doesn't apply to SQL 2008 as it has been pointed out (and I have not tested in 2008). However, my SQL 2005 instance conformed to those settings.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR

    SSC Guru

    Points: 281243

    I decided to go back and test this on SQL 2008 after my last post.

    I verified both Model database settings, and created a new database. I have not altered any Model database settings and for me, the sizes I see in both model and the new database correlate to the correct answer displayed in the QOD.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • This was removed by the editor as SPAM

  • SQLRNNR

    SSC Guru

    Points: 281243

    stewartc-708166 (1/20/2010)


    each version and edition is different

    the question should have been a bit more precise, i.e. specifying for which version / edition it was being asked

    That is true. Specifying version and edition would have been very helpful in this question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Fal

    SSCrazy

    Points: 2873

    As soon as I saw the question I thought it would be contentious. But my default out-of-the-box SQL 2005/2008 model db defaults to 3+1, ie: 4. I set it to a "better" size as part of my config of any new server so I've seen it a fair bit.

    But checking our hosted servers and our 2008 Express servers (whose sole purpose is mirror witnessing) which I haven't altered, the sizes remain 3+1.

    Now I'm curious about two things:

    1) is the model db size determine by OS version, locale, or something else nefarious; and

    2) how will this latest QotD biff turn out.

    Let me get my popcorn...

  • Arjun SreeVastsva

    SSCertifiable

    Points: 7135

    i agree with CirquedeSQLeil Spicifying Version and edition ...lot of viewers are in confusion

  • dawryn

    SSCarpal Tunnel

    Points: 4569

    Question is not precise enough :exclamationmark:

    Answer depends on model database for first files, next files get size of 1MB ➡ Books Online http://msdn.microsoft.com/en-us/library/ms176061.aspx

    When size is not supplied for the primary file, the Database Engine uses the size of the primary file in the model database. When a secondary data file or log file is specified but size is not specified for the file, the Database Engine makes the file 1 MB. The size specified for the primary file must be at least as large as the primary file of the model database.

    What about when model database is shrunk?

    Sql2k0 default: 1.25 MB

    Sql2k0 shrunk: 1.13 MB

    Sql2k5 default: 2.74 MB

    Sql2k5 shrunk: 1.75 MB

    What should be taken as right question? Probably any answer :blush: or none at all :w00t:

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    Since the author of the question didn't even bother to supply a reference for his (or her?) claims, I did his homework for him. The documentation on CREATE DATABASE is here: http://technet.microsoft.com/en-us/library/ms176061.aspx.

    The first interesting info is at the description of the SIZE parameter:

    When size is not supplied for the primary file, the Database Engine uses the size of the primary file in the model database. When a secondary data file or log file is specified but size is not specified for the file, the Database Engine makes the file 1 MB. The size specified for the primary file must be at least as large as the primary file of the model database.

    The question was not explicit about it, but let's for now assume that no secondary files are created (after all, the question did say "mdf/ldf"). That means that the size for the data will be equal to the size of the primary file of the model database - which can be different on each installation, since an administrator will often change it to ensure that new databases are of a certain size. (I have, for instance, once enlarged the model database on an instance to make sure that tempdb would be of sufficient size after each restart, instead of being allocated too small and causing many time consuming and fragmentation inducing autogrow events).

    The default log file size is only given in one of the examples (unless I overlooked something):

    A. Creating a database without specifying files

    The following example creates the database mytest and creates a corresponding primary and transaction log file. Because the statement has no <filespec> items, the primary database file is the size of the model database primary file. The transaction log is set to the larger of these values: 512KB or 25% the size of the primary data file. Because MAXSIZE is not specified, the files can grow to fill all available disk space.

    So the only correct answer is: "the initial size of a newly created database, when no sizes are specified, is equal to the size of the primary file of the model database on the same instance, plus the larger of 25% of that size or 512KB".

    On my system, creating a database without specifying any files caused the data file to be 2.1875 MB, and the log 0.546875 MB (according to sys.master_files). But I do not expect that to be the same for anyone else.

    I don't often agree with the poeple complaining about the quality of the QotD (though after having written some of them myself, I probably am more qualified to do so;-)), but in this case I'll make an exception. This question reallly is way below par. And that hurts especially after the already rather dubious question we had yesterday.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • vk-kirov

    SSCertifiable

    Points: 7686

    Hugo Kornelis (1/21/2010)


    The documentation on CREATE DATABASE is here: http://technet.microsoft.com/en-us/library/ms176061.aspx.

    The default log file size is only given in one of the examples (unless I overlooked something)

    It is given in the description of the LOG ON argument:

    LOG ON

    If LOG ON is not specified, one log file is automatically created that has a size that is 25 percent of the sum of the sizes of all the data files for the database or 512 KB, whichever is larger.

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    vk-kirov (1/21/2010)


    Hugo Kornelis (1/21/2010)


    The documentation on CREATE DATABASE is here: http://technet.microsoft.com/en-us/library/ms176061.aspx.

    The default log file size is only given in one of the examples (unless I overlooked something)

    It is given in the description of the LOG ON argument:

    LOG ON

    If LOG ON is not specified, one log file is automatically created that has a size that is 25 percent of the sum of the sizes of all the data files for the database or 512 KB, whichever is larger.

    Oooh, what a logical place .... NOT! 😉

    Thanks for finding it, and for posting it here.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 1 through 15 (of 89 total)

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