Database Size

Question

Database Size

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

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

sjimmo SSChampion Points: 11139 More actions · Answer 1

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

iBar SSCertifiable Points: 5872 More actions · Answer 2

SQL Server 2005 has following default settings of model DB.

MDF = 3mb

LDF = 1mb

So infact question is not appropriate.

José.Cruz Right there with Babe Points: 722 More actions · Answer 3

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

select @@version

/*

*/

sp_helpdb 'Model'

/*

namedb_sizeownerdbidcreatedcompatibility_level

model3.19 MBsa3Apr 8 200390

*/

/*

namefileidfilegroupsizemaxsizegrowthusage

modeldev1PRIMARY2240 KBUnlimited1024 KBdata only

modellog2NULL1024 KBUnlimited10%log only

*/

Created DB from SSMS:

sp_helpdb 'TesteSize'

/*

namedb_sizeownerdbidcreatedcompatibility_level

TesteSize4.00 MBsa12Jan 27 201090

*/

/*

namefileidfilegroupsizemaxsizegrowthusage

TesteSize1PRIMARY3072 KBUnlimited1024 KBdata only

TesteSize_log2NULL1024 KB2147483648 KB10%log only

*/

Created with CREATE DATABASE TestSize

sp_helpdb 'TestSize'

/*

namedb_sizeownerdbidcreatedcompatibility_level

TestSize2.73 MBsa13Jan 27 201090

*/

/*

namefileidfilegroupsizemaxsizegrowthusage

TestSize1PRIMARY2240 KBUnlimited1024 KBdata only

TestSize_log2NULL560 KB2147483648 KB10%log only

*/

I'm sure my Model Database was not changed.

José Cruz

saadla Valued Member Points: 63 More actions · Answer 4

saadla

Valued Member

Points: 63

January 28, 2010 at 2:34 am

#1109820

I Agree

wim.buyens Ten Centuries Points: 1159 More actions · Answer 5

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.

TomThomson SSC Guru Points: 104773 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

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)

sms1000 SSC Rookie Points: 42 More actions · Answer 8

sms1000

SSC Rookie

Points: 42

February 15, 2010 at 5:47 pm

#1119026

SQL 2008 3mb for mdf 1 for ldf

anna.ellis@getronics.com Default port Points: 1465 More actions · Answer 9

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.... 😉

Douglas Elwood SSC Eights! Points: 997 More actions · Answer 10

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.

Peter Trast SSCarpal Tunnel Points: 4392 More actions · Answer 11

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

Paul White SSC Guru Points: 150467 More actions · Answer 12

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

TomThomson SSC Guru Points: 104773 More actions · Answer 13

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

Peter Trast SSCarpal Tunnel Points: 4392 More actions · Answer 14

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