January 30, 2013 at 7:56 am
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
January 30, 2013 at 8:25 am
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.
January 30, 2013 at 9:43 am
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
January 30, 2013 at 9:54 am
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.
Jared
CE - Microsoft
January 30, 2013 at 10:02 am
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
January 30, 2013 at 10:52 am
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...
Jared
CE - Microsoft
January 30, 2013 at 7:02 pm
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
January 31, 2013 at 3:20 am
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
January 31, 2013 at 5:58 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply