SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Size


Database Size

Author
Message
Fal
Fal
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 1803
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...
dawryn
dawryn
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3374 Visits: 909
Question is not precise enough Exclamation Mark

Answer depends on model database for first files, next files get size of 1MB Arrow 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
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11128 Visits: 12004
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
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3728 Visits: 4408
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
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11128 Visits: 12004
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! Wink

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
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3728 Visits: 4408
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 Hehe
Peter Marriott
Peter Marriott
Old Hand
Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)

Group: General Forum Members
Points: 309 Visits: 109
Hi,

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

Cheers,

Peter
WayneS
WayneS
SSCrazy Eights
SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)

Group: General Forum Members
Points: 9996 Visits: 10574
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
Author - SQL Server T-SQL Recipes
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

Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11128 Visits: 12004
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
Richard M.
Richard M.
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1418 Visits: 2683
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search