Database Size

Question

Post reply

Database Size

Viewing 15 posts - 16 through 30 (of 88 total)

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

vk-kirov SSCertifiable Points: 7686 More actions · Answer 1

vk-kirov

SSCertifiable

Points: 7686

January 21, 2010 at 3:31 am

#1106570

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 Mr or Mrs. 500 Points: 528 More actions · Answer 2

Peter Marriott

Mr or Mrs. 500

Points: 528

January 21, 2010 at 3:50 am

#1106576

Hi,

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

Windows 7 x64

Cheers,

Peter

WayneS SSC Guru Points: 95405 More actions · Answer 3

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Hugo Kornelis SSC Guru Points: 64748 More actions · Answer 4

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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Richard M. SSCertifiable Points: 7288 More actions · Answer 5

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[/url]

Ray K SSC-Dedicated Points: 31135 More actions · Answer 6

Ray K

SSC-Dedicated

Points: 31135

January 21, 2010 at 7:11 am

#1106679

Add me to the angry chorus.

This is not a fair question. More detail is needed before we can give an honest answer.

I want my points back.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

sjimmo SSChampion Points: 11139 More actions · Answer 7

Technically, none of the answers are correct. The default size of a newly created database is based upon the size of the model database. I haven't worked with 2008, but 2005 and earlier, the default is 2MB for data with a 1MB T-Log, giving you a total size of 3MB. If you have adjusted the size of the model, then the new database will pick up that size as the default. Since this is not a selection available, I chose the norm, and got it wrong?

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

Brnbngls SSC Eights! Points: 813 More actions · Answer 8

I'm just upset that I got tripped up but not adding the values together. I knew that the mdf was 2mb and the ldf was 1mb. So when I saw the answers, I figured "Oh, maybe I'm wrong. It's 2 mb for each." But when I saw the answer, I realized I had made a dumb mistake.

And since I vehemently argued about ambiguity in QoTD yesterday, I can't very well defend this one. True, version differences and modifications to the model db make a difference. But I took this to mean (in essence) I've just installed SQL Server and now I'm creating a new database, what's the default sizes? But that my friends would creating a personal double standard.

The distance between genius and insanity is measured only by success.

jim_fleming SSC Enthusiast Points: 184 More actions · Answer 9

jim_fleming

SSC Enthusiast

Points: 184

January 21, 2010 at 7:52 am

#1106708

I agree with the other posts. Using SQL Server 2005, the model database is 3MB/1MB for the two files comprising the database. When I run create database myDB I get a 4MB database - 3MB for data and 1 for log.

Jim

vk-kirov SSCertifiable Points: 7686 More actions · Answer 10

sjimmo (1/21/2010)

but 2005 and earlier, the default is 2MB for data with a 1MB T-Log, giving you a total size of 3MB

For MSSQL 2000, the default size of model's MDF is 768 KB.

It was enlarged in MSSQL 2005 (http://technet.microsoft.com/en-us/library/ms143179(SQL.90).aspx):

Breaking Changes to Database Engine Features in SQL Server 2005

In SQL Server 2005, the model database contains the following changes:

- Larger minimum size.

- Compatibility level is set to 90.

- PAGE_VERIFY database option is set to CHECKSUM

david.wright-948385 SSCarpal Tunnel Points: 4032 More actions · Answer 11

sjimmo (1/21/2010)

Technically, none of the answers are correct. The default size of a newly created database is based upon the size of the model database. I haven't worked with 2008, but 2005 and earlier, the default is 2MB for data with a 1MB T-Log, giving you a total size of 3MB. If you have adjusted the size of the model, then the new database will pick up that size as the default. Since this is not a selection available, I chose the norm, and got it wrong?

I agree, and I didn't answer for that reason - the correct answer isn't in the options. The model database exists to give flexibility and conformity to new database creation.

I bet this will turn out to be another "the intention of QOD is to make people think" or "you can't comment on this if you haven't submitted a QOD" argument 🙂

sjimmo SSChampion Points: 11139 More actions · Answer 12

sjimmo

SSChampion

Points: 11139

January 21, 2010 at 8:26 am

#1106730

VK:

For MSSQL 2000, the default size of model's MDF is 768 KB

Sorry - I stand corrected. THe total size of SS2K and earlier is 2MB.

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

david.wright-948385 SSCarpal Tunnel Points: 4032 More actions · Answer 13

david.wright-948385

SSCarpal Tunnel

Points: 4032

January 21, 2010 at 8:28 am

#1106731

Hey, if points are returned/awarded do I get points for not answering cos the correct answer wasn't there? :hehe::w00t:

Peter Schott SSCrazy Eights Points: 9657 More actions · Answer 14

It's set to the size of the "model" database, regardless of edition. However, this really should have been more clear on version and specified that this would be for an unchanged model database.

I remembered the settings just because I generally change them. (Change log auto-grow option from 10%. For Dev systems, tend to set DB as Simple, etc.)

SanjayAttray SSChampion Points: 13157 More actions · Answer 15

I think question should be rephrased as what's the size of new DB on disk and also sql server version.

It is 2 Mb mdf + 1 MB ldf = 3 MB.

But if the initial size of of DB is to be considered it comes

sp_helpdb 'newdb'

-----------------

1.69 MB for SQL Server 2005.

I check it across some servers and its same on my environment.

SQL DBA.