March 25, 2011 at 7:13 am
Hi All,
How To check initial size of a DB, when we created it ?
for ex: If we create a Database with initial size as 1GB datafile and 256MB log file, now it grows to 10GB data file. Is there any mechanism to find out the initial size of that datafile when we actually created it ?
Thank You.
Regards,
Raghavender Chavva
March 25, 2011 at 8:58 am
Is that with a user db or just TEMPDB?
If you only mean TEMDB, the initial size as shown by the Files tab in db properties represents the inital startup value (ie what it will reset to when sql restarts).
HTH 🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave
March 25, 2011 at 11:46 am
If you are relying on default sizes (not altering them) when you create the databases, all you have to do is look at the size of the Model db files. All databases will be using, at a minimum, the same file sizes that Model is currently set at. TempDB starts at that size also.
But if you alter the sizes when you create your user dbs, then that won't help. And if you haven't restarted SQL Server in a long time, then TempDB has probably grown a lot since those original days.
What will you do with this info? That might assist us with finding a better answer.
March 25, 2011 at 11:50 am
If you are only requesting this info for tempdb, then the initial size will reset every time you restart SQL Server. Can we get more info on what you are trying to accomplish?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 25, 2011 at 12:18 pm
A new User databases size will be the same as the Model database. To get details of the files, their locations and sizes on any database, including tempdb, you could run:
sp_helpfile
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 26, 2011 at 10:22 pm
I got this question in a interview.
So we cannot get the size of database when we created it if we provide other than model database size ?
Thank You.
Regards,
Raghavender Chavva
March 27, 2011 at 3:22 am
Tempdb doesn't just use model.
have a look at:
select *
from sys.master_files
where database_id = 2
:w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 27, 2011 at 2:14 pm
ALZDBA (3/27/2011)
Tempdb doesn't just use model.:w00t:
Ummmm.... not quite right. From the first sentence you find when you lookup "Model Database" in BOL...
The model database is used as the template for [font="Arial Black"]all [/font]databases created on an instance of SQL Server. [font="Arial Black"]Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
March 27, 2011 at 2:55 pm
Jeff Moden (3/27/2011)
ALZDBA (3/27/2011)
Tempdb doesn't just use model.:w00t:
Ummmm.... not quite right. From the first sentence you find when you lookup "Model Database" in BOL...
The model database is used as the template for [font="Arial Black"]all [/font]databases created on an instance of SQL Server. [font="Arial Black"]Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.[/font]
Hence the composition of my reply.
the keyword ( is the positioned ) "just".
I didn't say it doesn't use model db, I said it doesn't just use model.
Which is not the same as "it just doesn't use model".
/*
Startup size of tempdb
*/
select *
from sys.master_files
where database_id = 2
order by file_id ;
/*
Current size of tempdb
*/
select *
from tempdb.sys.database_files
order by file_id ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 27, 2011 at 3:11 pm
Also, even though tempdb is recreated each time SQL Server starts, unlike a brand new user database, the new tempdb file settings, including it's size is taken from the existing tempdb configuration, while many other tempdb settings are taken from Model.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 27, 2011 at 5:39 pm
ALZDBA (3/27/2011)
I didn't say it doesn't use model db, I said it doesn't just use model.Which is not the same as "it just doesn't use model".
Ah... my bad. Sorry, Johan.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2011 at 12:30 am
Jeff Moden (3/27/2011)
ALZDBA (3/27/2011)
I didn't say it doesn't use model db, I said it doesn't just use model.Which is not the same as "it just doesn't use model".
Ah... my bad. Sorry, Johan.
No problem, Jeff.
I must learn to write it out using unambiguous sentences.
As you know, that may not be that obvious at the time you're writing it down.:unsure:
And my translation to English may just be inappropriate with regards to what I'm trying to explain.
e.g. things said in Dutch in a certain way, maybe aren't constructed in English in the way I translated them.
It is good you pointed to the bol ref.
By doing so, OP got extra info with regards to his quest and we worked away the ambiguity.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 28, 2011 at 5:09 am
[Jim].[dba].[Murphy] (3/27/2011)
Also, even though tempdb is recreated each time SQL Server starts, unlike a brand new user database, the new tempdb file settings, including it's size is taken from the existing tempdb configuration, while many other tempdb settings are taken from Model.
Not that I'm doubting you, but I've never heard of this. Could you point me to the exact reference? (A link or BOL search phrase)
March 28, 2011 at 12:46 pm
Brandie Tarvin (3/28/2011)
[Jim].[dba].[Murphy] (3/27/2011)
Also, even though tempdb is recreated each time SQL Server starts, unlike a brand new user database, the new tempdb file settings, including it's size is taken from the existing tempdb configuration, while many other tempdb settings are taken from Model.Not that I'm doubting you, but I've never heard of this. Could you point me to the exact reference? (A link or BOL search phrase)
A quick proof of concept is the multiple ndf file setup that we do in tempdb for certain environments. If that was lost, and it simply copied model, it wouldn't persist between reboots. It rebuilds to the original growth settings you give it.
I can't seem to find any good articles or BOL items on it though offhand.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 28, 2011 at 1:43 pm
In between, this is other question on TempDB only:
If we add multiple datafiles to tempdb database we need to keep all datafiles of same size.
Can any body tell me what is the exact reason for this ?
Is this implies only for TempDB or other user databases also ?
Thank You.
Regards,
Raghavender Chavva
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply