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 ««12345»»»

Database Size Expand / Collapse
Author
Message
Posted Thursday, January 21, 2010 12:10 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 3, 2014 9:19 PM
Points: 505, Visits: 1,691
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...




Post #851046
Posted Thursday, January 21, 2010 1:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:24 AM
Points: 2,461, Visits: 763
Question is not precise enough

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 or none at all
Post #851074
Posted Thursday, January 21, 2010 2:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:18 PM
Points: 6,056, Visits: 8,342
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #851089
Posted Thursday, January 21, 2010 3:20 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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.
Post #851105
Posted Thursday, January 21, 2010 3:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:18 PM
Points: 6,056, Visits: 8,342
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #851112
Posted Thursday, January 21, 2010 3:31 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
Let me make a guess
The main purpose of QODs is education.
For most QODs it is education of the community, but for some QODs it is education of the question authors
Post #851113
Posted Thursday, January 21, 2010 3:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, June 14, 2014 2:41 AM
Points: 283, Visits: 100
Hi,

On my Developer edition both 2005 and 2008 come up with 3MB and 1MB.
Windows 7 x64

Cheers,

Peter
Post #851120
Posted Thursday, January 21, 2010 5:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:39 PM
Points: 5,365, Visits: 8,964
stewartc-708166 (1/20/2010)
Beg to differ
when creating a new SQL2008 db, the mdf defaults to 3MB and the ldf to 1MB.

On my SQL2008 (Developer Edition, x64), the modeldev.mdf is 2MB and the modellog.ldf is 1MB.
I wonder if there are differences between editions and platforms?
What about if it is a named instance?
My SQL2008 install was to a named instance, with SQL2005 (Developer, x64) running on a different named instance (not a 2005 upgrade to 2008).


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #851165
Posted Thursday, January 21, 2010 5:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:18 PM
Points: 6,056, Visits: 8,342
WayneS (1/21/2010)
stewartc-708166 (1/20/2010)
Beg to differ
when creating a new SQL2008 db, the mdf defaults to 3MB and the ldf to 1MB.

On my SQL2008 (Developer Edition, x64), the modeldev.mdf is 2MB and the modellog.ldf is 1MB.
I wonder if there are differences between editions and platforms?
What about if it is a named instance?

All not really relevant, since the DBA can change the size of the model database. So regardless of edition, platform, and instance - the size will still be whatever the DBA has assigned to the model database.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #851169
Posted Thursday, January 21, 2010 6:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 27, 2014 4:57 AM
Points: 1,093, Visits: 2,618
And once again, another flawed simple question is busted by lack of attention on how the question is formulated:
a) No SQL version supplied
b) No indication whether it is an unaltered Model DB. In my case, some of the Model DB's we have altered sizes for business reasons, so in those cases none of the answers would be correct.....




_______________________________________________________________________
For better assistance in answering your questions, click here
Post #851190
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse