Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Size


Database Size

Author
Message
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3494 Visits: 4408
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
timfle
Right there with Babe
Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)

Group: General Forum Members
Points: 718 Visits: 112
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
vk-kirov
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3494 Visits: 4408
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"? :-D 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
vk-kirov
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3494 Visits: 4408
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
timfle
Right there with Babe
Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)

Group: General Forum Members
Points: 718 Visits: 112
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



sjimmo
sjimmo
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3351 Visits: 2841
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
iBar
Mr or Mrs. 500
Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)

Group: General Forum Members
Points: 586 Visits: 840
SQL Server 2005 has following default settings of model DB.

MDF = 3mb
LDF = 1mb

So infact question is not appropriate.
José.Cruz
José.Cruz
SSC Veteran
SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)

Group: General Forum Members
Points: 256 Visits: 4051
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
saadla
saadla
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 4
I Agree
wim.buyens
wim.buyens
SSC Veteran
SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)

Group: General Forum Members
Points: 277 Visits: 188
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search