January 20, 2010 at 9:29 pm
Comments posted to this topic are about the item Database Size
January 20, 2010 at 10:05 pm
This was removed by the editor as SPAM
January 20, 2010 at 10:14 pm
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 😀
January 20, 2010 at 11:21 pm
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 ?
January 20, 2010 at 11:44 pm
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.
January 20, 2010 at 11:49 pm
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
January 20, 2010 at 11:53 pm
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
January 20, 2010 at 11:57 pm
This was removed by the editor as SPAM
January 21, 2010 at 12:02 am
stewartc-708166 (1/20/2010)
each version and edition is differentthe 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
January 21, 2010 at 12:10 am
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...
January 21, 2010 at 12:10 am
i agree with CirquedeSQLeil Spicifying Version and edition ...lot of viewers are in confusion
January 21, 2010 at 1:22 am
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:
January 21, 2010 at 2:16 am
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.
January 21, 2010 at 3:20 am
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.
January 21, 2010 at 3:29 am
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.
Viewing 15 posts - 1 through 15 (of 88 total)
You must be logged in to reply to this topic. Login to reply