• 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/