| Author |
Topic |
|
maassql
Starting Member
13 Posts |
 Posted - 2008-03-11 : 16:45:13
|
| Via t-sql, how can I query for a file's initial size?I want the same thing one finds by :Start SQL Server Management Studio, view, object explorer, right click [dbname], properties, files, Database files: Initial Size (MB).Many thanks.Anything will give up its secrets if you love it enough. -- George Washington Carver |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-11 : 22:34:28
|
| Try query sys.sysfiles. |
 |
|
|
maassql
Starting Member
13 Posts |
Posted - 2008-03-12 : 10:37:39
|
| Appreciate the post. Perhaps I am missing something, but original size is not in sys.files, sys.database_files, or sys.master_files. Have explored a bit further, but haven't found the right spot just yet.Anything will give up its secrets if you love it enough. -- George Washington Carver |
|
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-03-12 : 10:42:37
|
| sp_helpfile ?or sp_helpdb ?Em |
|
|
|
maassql
Starting Member
13 Posts |
Posted - 2008-03-12 : 11:03:55
|
| Thanks again.Neither sp_helpfile or sp_helpdb seem to hold original size.Anything will give up its secrets if you love it enough. -- George Washington Carver |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-03-12 : 15:00:21
|
here you go:select name, size * 8 AS [Initial Size in KB], size * 8 / 1024.00 AS [Initial Size in MB]from sys.master_files where database_id = DB_ID() _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
maassql
Starting Member
13 Posts |
Posted - 2008-03-12 : 15:43:30
|
| Mladen:2005 BOL:sys.master_files (Transact-SQL) size - int - Current file size, in 8-KB pages. For a database snapshot, size reflects the maximum space that the snapshot can ever use for the file.Which is what I was going by.I think that I misunderstood the definition of Initial size on the screen in SSMS: Initial Size"Enter or modify the initial size for the file in megabytes. This defaults to the value of the model database." For some reason, I was thinking "initial" meant "creation" size, I guess b/c the reference to model database. But really, what initial refers to is size when this form was "initially" opened.dbcc shrinkfile does give back a MinimumSize column, aka, "This corresponds to the minimum size or originally created size of a file." exactly what I want, but... a) that data can not be grabbed in T-SQL ( that I know of ) b/c b) Even if I could get that information, I can't get to it w/o running DBCC shrinkfileAnything will give up its secrets if you love it enough. -- George Washington Carver |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-03-12 : 15:49:09
|
| that is the what ssms uses for initial size.you can see this if you run profiler and open properties on the database_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-12 : 22:55:14
|
| >> original size is not in sys.files, sys.database_files, or sys.master_files. Did you check sys.sysfiles? |
|
|
|
maassql
Starting Member
13 Posts |
Posted - 2008-03-13 : 11:55:03
|
I should amend my first post as such:
Via t-sql, how can I query for a file's initial size at creation or shrinkable to size?I do not want the same thing one finds by :Start SQL Server Management Studio, view, object explorer, right click [dbname], properties, files, Database files: Initial Size (MB). What I want instead is a number like the one found in dbcc shrinkfile named MinimumSize, per BOL: "This corresponds to the minimum size or originally created size of a file."Many thanks.
My original confusion came from the fact that I read the SSMS file GUI screen caption "Initial size" to mean "Initial / Created size of the file". I did not see Initial size to mean "size of file when this GUI window opened." While it seems very obvious to me that would be the information most anyone would want out of that screen, I still don't find the caption accurate. Whilst it is the most likely property to change on the screen, all of them are subject to change while the GUI screen is up, so the same logic could have applied to all of the properties "Initial Name", "Initial location", "Initial size". Anyways, I originally did a trace on SSMS, and didn't find the creation size being passed back, combined with my assumption, declared in my head that SSMS must be obtaining the info from some source other than a query, or I somehow screwed up the trace.The closest to ananswer I have come to is DBCC SQLPERF ( LOGSPACE ). That of course shows # of free space, but not the original size by a long shot. sys.sysfiles is a compability view which shows the same information ( relevant to my quesion ) as sys.master_files and sys.database_files - the current size.I would love to have the mud wiped from my eyes....I'm sure the information is here somewhere.Thanks for all the responses!Anything will give up its secrets if you love it enough. -- George Washington Carver |
|
|
|
|
file initial size