Database Size

Question

Post reply

Database Size

Viewing 15 posts - 61 through 75 (of 88 total)

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

Stibally g SSC Journeyman Points: 82 More actions · Answer 1

YEs! I want my point back.. it would be my first... 🙁

Stibally 😀

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

Mine is wrong too! mdf 3 MB + LDF 1 MB = 4 MB for SQL 2005 EE.

Question should be more accurate but anyway I enjoy all this discussion 😛

Cheers!

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

The moderator or who ever checks the questions in QOD can make it compulsory to include the specific version of SQL or have a option that says the question implies to all the versions of SQL.

"Keep Trying"

Mauve SSChampion Points: 11316 More actions · Answer 4

Lynn Pettis (1/21/2010)
No luck, knowledge. What issues? I simply assumed a default install of SQL Server 2005 (none was specified), made a decision based on what I have seen when creating simple sandbox databases at home. Had I looked at SQL Server 2008, I'd have gotten wrong, as then default size of model has changed.

Interesting, as my default (out of the box) SQL Server 2005 and 2008 developer editions create a 3+1 MB database. I'm curious as to why yours is 2+1. Was it upgraded from SQL Server 2000? Or does it have something to do with options selected.

In any event, the resulting "default" size is immaterial, as it is based upon the size [du jour] of the model database.

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Tom Garth SSCertifiable Points: 6193 More actions · Answer 5

One more log for the fire.

I ran a simple CREATE DATABASE mydb using SQL Query Analyzer against my 2005 Developer edition, and the size came out to 1.68 compared to 1.69 for the model. The disk size of both mdf files was 1216 KB.

Then I used the 2005 SSMS UI to create a 'New database' named mydb2 and the resulting size is 3MB. 2MB mdf and 1MB ldf. My model db is still 1216 KB.

Go figure...

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

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

Tom Garth (1/22/2010)
I ran a simple CREATE DATABASE mydb
...
Then I used the 2005 SSMS UI to create a 'New database'
...

Bravo, Tom!

Of course SSMS does not use the 'CREATE DATABASE xxx' statement without parameters, it explicitly defines all the file names, file sizes etc.

How didn't I see that the author had created his database via SSMS :blush:

So the answer does not (explicitly) depend on model's size, it depends on some SSMS internal algorithm!

Edit: silly miswording corrected

Mauve SSChampion Points: 11316 More actions · Answer 7

vk-kirov (1/22/2010)
So the answer does not (explicitly) depend on model's size, it depends on some SSMS internal algorithm!

So I'll repeat my earlier statement that the correct answer should have been: "It depends (on lots of things)"

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Halcyon Ten Centuries Points: 1331 More actions · Answer 8

Halcyon

Ten Centuries

Points: 1331

January 22, 2010 at 1:03 pm

#1107534

development edition 2005, 64 bit - 3 and and 1 mb

timfle SSC Eights! Points: 932 More actions · Answer 9

does anyone read MS documentation? Do you people even have the fainest idea what the "MODEL" database is used?

Tom Garth SSCertifiable Points: 6193 More actions · Answer 10

timfle (1/22/2010)
does anyone read MS documentation? Do you people even have the fainest idea what the "MODEL" database is used?
Obviously vk kirov doesn't (although I have worked with people from russia - and for the most part they are the posterior part of an ***)
Although we all know that the russian version of sql server is different from the other versions because of security concerns.
And I am sure i will cease to exist after vk kirov turns my name over to the russian mafia

It would be okay by me if you just ceased to exist on SSC!

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

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

timfle (1/22/2010)
does anyone read MS documentation?
Obviously vk kirov doesn't

Of course I may have overlooked something. Could you please post a suitable link to BOL and explain my mistake? I'll be very grateful to you.

timfle (1/22/2010)
Although we all know that the russian version of sql server is different from the other versions because of security concerns.

Shame on me, but I've never seen Russian versions of MSSQL (they do exist, of course (2005 and 2008)) 🙂

timfle SSC Eights! Points: 932 More actions · Answer 12

timfle (1/22/2010)

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

does anyone read MS documentation?

Obviously vk kirov doesn't

Of course I may have overlooked something. Could you please post a suitable link to BOL and explain my mistake? I'll be very grateful to you.

timfle (1/22/2010)

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

Although we all know that the russian version of sql server is different from the other versions because of security concerns.

Shame on me, but I've never seen Russian versions of MSSQL (they do exist, of course (2005 and 2008))

ok vk-kirov

link to 2008 BOL: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/29ddac46-7a0f-4151-bd94-75c1908c89f8.htm

excerpt of text:

size

Is the initial size of the file.

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.

my comments about russian version of SQL Server were SARCASM - as we all know - if this were the old Soviet union days - SQL Server would have been invented in USSR and stolen by american capitalist pigs

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

timfle (1/23/2010)
link to 2008 BOL: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/29ddac46-7a0f-4151-bd94-75c1908c89f8.htm
excerpt of text:
size
Is the initial size of the file.
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.

1) How does this correlate with your earlier statement (The answer is the same size as the model database)? As we can see, the size of the newly created log file will not be equal to the model db log file size (unless modellog.ldf = 1 MB).

2) How about the case when log file specifications are missing at all? (e.g. 'CREATE DATABASE TestDB' – even this simple statement can be run)

BOL say what will happen (please check the link you provided):

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.

Once again, the size of the newly created log file will not be equal to the model db log file size.

3) The author of the question says in the explanation he created the database via SSMS UI. In this case SSMS specifies all file properties. For example, my SSMS 2008 produces this script:

CREATE DATABASE [TestDB] ON PRIMARY

( NAME = N'TestDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDB.mdf' ,

SIZE = 3072KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'TestDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf' ,

SIZE = 1024KB , FILEGROWTH = 10%)

What is the relation between this code and the model database size?

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

timfle (1/23/2010)
as we all know - if this were the old Soviet union days - SQL Server would have been invented in USSR and stolen by american capitalist pigs

Excuse me, who are these mysterious "we all"? 😀 Please don't tell that "we all" = "my very self" :hehe:

timfle (1/23/2010)
my comments about russian version of SQL Server were SARCASM

We all see your great sense of humor. Stand-up comedy scene is waiting for you 😉

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

vk-kirov (1/23/2010)
3)
CREATE DATABASE [TestDB] ON PRIMARY
( NAME = N'TestDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDB.mdf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TestDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf' ,
SIZE = 1024KB , FILEGROWTH = 10%)
What is the relation between this code and the model database size?

By the way, the size of my model.mdf is 2304 KB, the size of my modellog.ldf is 512 KB; these values are not equal to 3072 and 1024 from the script.

timfle SSC Eights! Points: 932 More actions · Answer 15

kirov - as you think this is a matter of such great concern - please call microsoft and pay them to explain

I for one don;t really give a horse's posterior and in all the years I have been doing dba work IT HAS NEVER MATTERED