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

"kind of bug" with sql server 2008 Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2013 7:56 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 20, 2014 10:36 PM
Points: 880, Visits: 4,081
Seems like I posted my original post in wrong topic so putting it here again. Please ignore my old post http://www.sqlservercentral.com/Forums/Topic1413575-1292-1.aspx as I dont have option to delete it from there..

As I mentioned in my original post :-

I am not understanding this behaviour of SQL Server 2008 (ent ed, sp3cu2). I configured my model database with adequate space so that any new database have default configuration of model.
Now the problem is,
When I am creating it via ssms qizrd, its creating the data and log files as exact of model.
But,
If I run simple tsql as "create database test", it still not creating the data and log file as per model. And also its not consistent of default things which is 3 mb for data and 1 mb for log (initial size). In some environment, this value also changing.

Can someone point me right direction to understand this?

I am not sure about other edition or version but my build level is 10.0.5768.0(sql 2008, sp3cu2) , 64 bit server and 64 bit sql where I am having this issue.

So is this kind of big and is anyone else aware of it?


----------
Ashish
Post #1413640
Posted Wednesday, January 30, 2013 8:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:28 AM
Points: 1,539, Visits: 8,128
When you use the Wizard, have a look at the SQL code it creates ('Script Actions to a New Query Window'), and you'll see that it specifies the file sizes taken from the Model database.
Several settings are specified via the Wizard that aren't when you simply run CREATE DATABASE with the minimum information.


BrainDonor
Linkedin
Blog Site
Post #1413658
Posted Wednesday, January 30, 2013 9:43 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 20, 2014 10:36 PM
Points: 880, Visits: 4,081
Thanks Brian for the reply. I am aware of all those things but the points is "when you create the new user database without any specific configuration, it should get created as per model database setting" but in this case it is not.
The steps to replicate the issue :-
1) Modify the model databases's data and log file size to something else.
2) Now create the database via plain tsql as "create database test" and this doesn't create the database as similar to model.



----------
Ashish
Post #1413693
Posted Wednesday, January 30, 2013 9:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:07 PM
Points: 2,691, Visits: 3,371
After reviewing BOL, I do not see anywhere that it states that the file sizes are copied from model. In fact, the only reference I see to file size is this:
The sizes of these files can vary slightly for different editions of SQL Server.


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1413697
Posted Wednesday, January 30, 2013 10:02 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:08 AM
Points: 1,855, Visits: 2,962
SQLKnowItAll (1/30/2013)
After reviewing BOL, I do not see anywhere that it states that the file sizes are copied from model. In fact, the only reference I see to file size is this:
The sizes of these files can vary slightly for different editions of SQL Server.


CREATE DATABASE has this:

SIZE size
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.


I just tried on a dev machine and I can't reproduce crazy4sql's behaviour though - my new DB has the same file sizes as my model DB.

Cheers
Gaz
Post #1413701
Posted Wednesday, January 30, 2013 10:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:07 PM
Points: 2,691, Visits: 3,371
Gazareth (1/30/2013)
SQLKnowItAll (1/30/2013)
After reviewing BOL, I do not see anywhere that it states that the file sizes are copied from model. In fact, the only reference I see to file size is this:
The sizes of these files can vary slightly for different editions of SQL Server.


CREATE DATABASE has this:

SIZE size
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.


I just tried on a dev machine and I can't reproduce crazy4sql's behaviour though - my new DB has the same file sizes as my model DB.

Cheers
Gaz
Nice, I should have thought to look there... I always specify my size in the script, so...


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1413718
Posted Wednesday, January 30, 2013 7:02 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 20, 2014 10:36 PM
Points: 880, Visits: 4,081

I just tried on a dev machine and I can't reproduce crazy4sql's behaviour though - my new DB has the same file sizes as my model DB.


Thanks for trying Gaz. Did you tried after modifying the model data and log file size? My environment is sql 2008 sp3cu2 where I can replicate the issue.


----------
Ashish
Post #1413816
Posted Thursday, January 31, 2013 3:20 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:08 AM
Points: 1,855, Visits: 2,962
SQLKnowItAll (1/30/2013)
I always specify my size in the script, so...


Yep, me too.

crazy4sql (1/30/2013)

I just tried on a dev machine and I can't reproduce crazy4sql's behaviour though - my new DB has the same file sizes as my model DB.


Thanks for trying Gaz. Did you tried after modifying the model data and log file size? My environment is sql 2008 sp3cu2 where I can replicate the issue.


Yep, changed data file to 200MB and log to 20MB on model (from defaults 3MB/1MB).
Then ran "create database model_test" - files were created as 200MB & 20MB.
That's on 2008R2 SP2. I don't have any 2008 builds available at the moment, so can't test on your specific version.

Cheers
Gaz
Post #1413956
Posted Thursday, January 31, 2013 5:58 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 20, 2014 10:36 PM
Points: 880, Visits: 4,081

Yep, changed data file to 200MB and log to 20MB on model (from defaults 3MB/1MB).
Then ran "create database model_test" - files were created as 200MB & 20MB.
That's on 2008R2 SP2. I don't have any 2008 builds available at the moment, so can't test on your specific version.


Thanks Gaz. Yes, we also follow the same line that every database should have the details of initial size and autogrowth but yesterday when in of the random checks, I found this surprising behaviour. And still same....


----------
Ashish
Post #1414047
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse