SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


"kind of bug" with sql server 2008


"kind of bug" with sql server 2008

Author
Message
crazy4sql
crazy4sql
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1671 Visits: 4489
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
Steve Hall
Steve Hall
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2802 Visits: 11551
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.

Steve Hall
Linkedin
Blog Site
crazy4sql
crazy4sql
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1671 Visits: 4489
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
Jared Karney
Jared Karney
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4958 Visits: 3694
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Gazareth
Gazareth
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3985 Visits: 5804
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
Jared Karney
Jared Karney
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4958 Visits: 3694
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
crazy4sql
crazy4sql
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1671 Visits: 4489

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
Gazareth
Gazareth
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3985 Visits: 5804
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
crazy4sql
crazy4sql
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1671 Visits: 4489

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
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