Post reply

Database Size

  • January 24, 2010 at 9:17 am

    #1107918

    To Timfle and VK:

    Tim, I for one take great offense as to your "SARCASM", it is truely non-professional, from an individual who claims to have so much time being professional.

    Both: the bickering back and forth is doing nothing more than taking bandwidth and showing a narrow-mindness from at least one of you.

    As for the links:

    msdn - http://msdn.microsoft.com/en-us/library/ms186388.aspx (SS2008, but is applicable to ALL previous versions)

    BOL - 2005 ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/4e4f739b-fd27-4dce-8be6-3d808040d8d7.htm

    Also - http://www.sql-server-performance.com/articles/dba/System_Databases_in_SQL_Server_p1.aspx

    These are just a few.

    The model database has worked this way since the creation of the Sybase SQL Server database system, from which MS SQL Server came from.

    So can we NOT try to start WWIII and the recreation of the USSR?

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

  • January 26, 2010 at 8:16 am

    #1108713

    SQL Server 2005 has following default settings of model DB.

    MDF = 3mb

    LDF = 1mb

    So infact question is not appropriate.

  • January 27, 2010 at 8:17 am

    #1109322

    Just to share my results, i've just tried it on my test environement:

    select @@version

    /*

    Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) May 26 2009 14:24:20 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 6.1 (Build 7600: )

    */

    sp_helpdb 'Model'

    /*

    name db_size owner dbid created compatibility_level

    model 3.19 MB sa 3 Apr 8 2003 90

    */

    /*

    name fileid filegroup size maxsize growth usage

    modeldev 1 PRIMARY 2240 KB Unlimited 1024 KB data only

    modellog 2 NULL 1024 KB Unlimited 10% log only

    */

    Created DB from SSMS:

    sp_helpdb 'TesteSize'

    /*

    name db_size owner dbid created compatibility_level

    TesteSize 4.00 MB sa 12 Jan 27 2010 90

    */

    /*

    name fileid filegroup size maxsize growth usage

    TesteSize 1 PRIMARY 3072 KB Unlimited 1024 KB data only

    TesteSize_log 2 NULL 1024 KB 2147483648 KB 10% log only

    */

    Created with CREATE DATABASE TestSize

    sp_helpdb 'TestSize'

    /*

    name db_size owner dbid created compatibility_level

    TestSize 2.73 MB sa 13 Jan 27 2010 90

    */

    /*

    name fileid filegroup size maxsize growth usage

    TestSize 1 PRIMARY 2240 KB Unlimited 1024 KB data only

    TestSize_log 2 NULL 560 KB 2147483648 KB 10% log only

    */

    I'm sure my Model Database was not changed.

    José Cruz

  • January 28, 2010 at 2:34 am

    #1109820

    I Agree

  • January 28, 2010 at 8:41 am

    #1110107

    this is not a good question because you can set sql so that a new database is initiated with 1gb of size.

    This is the 3d question today which the question or possible answers are not correct, and i don't mean to I answered them wrong.

  • February 14, 2010 at 7:52 am

    #1118571

    I agree with many of the comments so far: this was not a good QOTD.

    The correct answer is that the a default create with no parameters supplied other than the name will create a db with primary data file size same as in the model db and log file size 1MB -or maybe not 1MB but something else? I think Hugo gave some strange rules for initial log file sizes which are not the same as those given in the BOL entries for Create Database in T-SQL Reference for SQL2008, for SQL2005, or for SQL2000, and he referenced MSDN documentation for them.

    The options for the answer all all just numbers of MB, so the question needs to specify the version of SQL Server (and maybe whether it is an evaluation copy or some beta or the ready to go version), and even then the person trying to answer would have to assume that it was an implied rule that the model database size was as shipped by MS - and even with that assumption the edition of SQL Server as well as the version would have to be stipulated, because the size of the model database files varies from edition to edition: that the sizes of the model database files are not the same in all editions is documented in BOL: for SQLS 2005 in http://msdn.microsoft.com/en-us/library/ms186388(SQL.90).aspx, for SQL 2008 in http://msdn.microsoft.com/en-us/library/ms186388.aspx, and for SQL 2000 in http://msdn.microsoft.com/en-us/library/aa174522(SQL.80).aspx.

    Tom

  • February 14, 2010 at 6:59 pm

    #1118644

    Heh... the correct answers are actually missing for all versions.... "Based on Model" and "Too Small". 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 15, 2010 at 5:47 pm

    #1119026

    SQL 2008 3mb for mdf 1 for ldf

  • February 18, 2010 at 3:52 am

    #1120766

    Jeff Moden (2/14/2010)

    Heh... the correct answers are actually missing for all versions.... "Based on Model" and "Too Small". 😛

    Best response yet, IMHO.... 😉

  • February 26, 2010 at 4:52 pm

    #1125869

    It appears there is confusion rampant with this question.

    It asks how large is the database. The question does *NOT* say anything about what are the total sizes of the files created.

    Since the .mdf file is essentially the "database" and the .ldf file is the transaction log, this question is poorly worded, badly formed and subject to existing sizes in the model database - which is well beyond the scope (or probably the intention) of the question.

    The version issues (SQL 2000/2005/2008) notwithstanding, this is just someone trying to be cute, not ask a meaningful question.

    I love the questions that make me think or teach me something - neither of these attributes were present in this question.

    Sorry to be harsh, but questions like this should not be posted.

  • March 15, 2010 at 10:24 pm

    #1134240

    Not sure where all of these 3mb/1mb numbers are coming from but when I do fresh installs of SQL 2005 or 2008 (which I do a couple of times a month in the classroom from a dvd) the new databases always default to 2mb for mdf and 1mb for ldf and that is for dev, standard or ent editions. I also checked my production servers on 2005 and 2008 and I could not find one that defaulted to anything else... great question and sort of has me thinking, but that creates 2 more questions:

    1. Who is going to use a 2MB database?

    2. Didn't someone say "size doesn't matter"? 😉

    Peter Trast
    Microsoft Certified ...(insert many literal strings here)
    Microsoft Design Architect with Alexander Open Systems

  • March 30, 2010 at 10:28 am

    #1142436

    I do love an ambiguous/controversial QotD: the discussion is always fascinating.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • March 31, 2010 at 4:47 am

    #1142951

    Paul White NZ (3/30/2010)

    I do love an ambiguous/controversial QotD: the discussion is always fascinating.

    Fascinating isn't the word I would use for this discussion: the number of different wrong answers suggested is amazing, very few people seem to have noticed that there is no correct answer available. Perhaps "Astounding" would be a better word ths time (I hesitate to suggest "Appalling" because there were a few good comments).

    Tom

  • March 31, 2010 at 8:12 am

    #1143104

    Tom.Thomson (3/31/2010)

    Paul White NZ (3/30/2010)

    I do love an ambiguous/controversial QotD: the discussion is always fascinating.

    Fascinating isn't the word I would use for this discussion: the number of different wrong answers suggested is amazing, very few people seem to have noticed that there is no correct answer available. Perhaps "Astounding" would be a better word ths time (I hesitate to suggest "Appalling" because there were a few good comments).

    Surprising is my word. Just shows how little you need to understand the product to use and administer it. No wonder Microsoft rules the software world and I can be a DBA...

    Peter Trast
    Microsoft Certified ...(insert many literal strings here)
    Microsoft Design Architect with Alexander Open Systems

Viewing 14 posts - 76 through 88 (of 88 total)

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