Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789»»

Database Size Expand / Collapse
Author
Message
Posted Saturday, January 23, 2010 10:54 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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))
Post #852563
Posted Saturday, January 23, 2010 1:40 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, August 25, 2014 1:58 PM
Points: 718, Visits: 111
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



Post #852607
Posted Saturday, January 23, 2010 5:57 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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"

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
Post #852654
Posted Saturday, January 23, 2010 6:10 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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.
Post #852655
Posted Saturday, January 23, 2010 7:27 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, August 25, 2014 1:58 PM
Points: 718, Visits: 111
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



Post #852660
Posted Sunday, January 24, 2010 9:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:47 AM
Points: 2,919, Visits: 2,525
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
Post #852735
Posted Tuesday, January 26, 2010 8:16 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 6:22 AM
Points: 574, Visits: 800
SQL Server 2005 has following default settings of model DB.

MDF = 3mb
LDF = 1mb

So infact question is not appropriate.
Post #853688
Posted Wednesday, January 27, 2010 8:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 24, 2014 9:16 AM
Points: 247, Visits: 3,868
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
Post #854426
Posted Thursday, January 28, 2010 2:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 28, 2010 2:32 AM
Points: 49, Visits: 4
I Agree
Post #855014
Posted Thursday, January 28, 2010 8:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 237, Visits: 135
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.
Post #855343
« Prev Topic | Next Topic »

Add to briefcase «««56789»»

Permissions Expand / Collapse