April 16, 2012 at 3:06 pm
balasach82 (4/16/2012)
Perry,Size for all the new files would be set to 1000 (mb)
Why 1000MB, 1GB = 1024MB?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 17, 2012 at 5:51 am
😀 Perry,
you are right. its 1024Mb, not 1000mb
April 17, 2012 at 6:57 am
balasach82 (4/17/2012)
😀 Perry,you are right. its 1024Mb, not 1000mb
😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 21, 2012 at 4:31 am
Perry/Gail,
Had this strange doubt. After dividing and increasing the Data/log file size, If the SQL is rebooted later as part of regular maintenance, then tempdb would be created by making a copy of model (which is set for 8mb data and 1mb log), which defeats the purpose of tempdb division.
So, should model too, have the same number of data files as well as size as that of tempdb (1mdf +7 new mdf +1log).
April 21, 2012 at 4:40 am
balasach82 (4/21/2012)
If the SQL is rebooted later as part of regular maintenance, then tempdb would be created by making a copy of model (which is set for 8mb data and 1mb log), which defeats the purpose of tempdb division.
No, not at all. TempDB is cleared, not copied from model
So, should model too, have the same number of data files as well as size as that of tempdb (1mdf +7 new mdf +1log).
No, that makes no sense and would result in any user database created getting multiple files.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2012 at 5:23 am
Thanks Gail for the immediate clarification. I too was wondering that it does not make sense to modify model (which eventually would have an effect on the new user databases).
A dumb question maybe....if the tempdb does not use model as a template, then how does it gets created when the data/log files are not there? Is it making use of internal scripts/process to create the tempdb?
Also, does DB instant file initialization is recommended?
April 21, 2012 at 5:46 am
It's a lot more complex than 'TempDB uses model as a template'. TempDB isn't actually recreated on startup. It's just cleared and the contents made the same as model.
Now, if the files aren't there they have to be created, but that will use the settings for TempDB in the system catalogs, not model, though it might use the files from Model.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2012 at 5:48 am
balasach82 (4/21/2012)
Thanks Gail for the immediate clarification. I too was wondering that it does not make sense to modify model (which eventually would have an effect on the new user databases).A dumb question maybe....if the tempdb does not use model as a template, then how does it gets created when the data/log files are not there? Is it making use of internal scripts/process to create the tempdb?
Also, does DB instant file initialization is recommended?
If you shut down the SQL server instance and delete the tempdb files, when you restart the service the database will be recreated using the structure of model only, the size and growth etc will be used from the settings in sys.master_files.
It's no different for a user db, say model mdf is set size=1024MB and you run a create db statement for a new user db with size=2048MB, which do you think will be used?
Now create a new db without specifying a size what is used then?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 21, 2012 at 6:09 am
Gail,
So, now my understanding it that, during reboot, tempdb does make use of model to an extent, but it does not get the size details. Those are got from system tables. Am I correct?
Perry,
While creating a DB, if the size is not given, it takes the size of the model, whereas if the size is given (which is greater/lesser than model size), then the greater/lesser size is taken. Please correct me if my understanding is wrong
April 21, 2012 at 6:40 am
balasach82 (4/21/2012)
Gail,So, now my understanding it that, during reboot, tempdb does make use of model to an extent, but it does not get the size details. Those are got from system tables. Am I correct?
TempDB is not recreated after a restart. The files aren't deleted and recreated. It's reset to the specified sizes and the database is cleared.
If the tempDB files are deleted, then when SQL starts it gets the files from model and sets them to the sizes defined in the system catalogs
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2012 at 7:41 am
Thanks very much Gail (and sorry for testing your/Perry patience).
"tempdb is cleared and reset to the specified sizes in the system catalogs"
this makes it clear to my soft brain.
April 21, 2012 at 10:51 am
balasach82 (4/21/2012)
Perry,While creating a DB, if the size is not given, it takes the size of the model, whereas if the size is given (which is greater/lesser than model size), then the greater/lesser size is taken. Please correct me if my understanding is wrong
The size would typically never be less than model, unless you've changed models file sizes from th default.
If you specify the file size it will use it, if not the model will be used. Model defaults are 3MB and 1MB for data and log respectively.
balasach82 (4/21/2012)
Thanks very much Gail (and sorry for testing your/Perry patience)."tempdb is cleared and reset to the specified sizes in the system catalogs"
this makes it clear to my soft brain.
Here's a test for you, open up model db and create a table. Restart your SQL server instance and go look in tempdb!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply