SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 file initial size
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

maassql
Starting Member

13 Posts

Posted - 03/11/2008 :  16:45:13  Show Profile  Visit maassql's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 03/11/2008 :  22:34:28  Show Profile  Reply with Quote
Try query sys.sysfiles.
Go to Top of Page

maassql
Starting Member

13 Posts

Posted - 03/12/2008 :  10:37:39  Show Profile  Visit maassql's Homepage  Reply with Quote
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
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 03/12/2008 :  10:42:37  Show Profile  Reply with Quote
sp_helpfile ?
or sp_helpdb ?

Em

Edited by - elancaster on 03/12/2008 10:45:20
Go to Top of Page

maassql
Starting Member

13 Posts

Posted - 03/12/2008 :  11:03:55  Show Profile  Visit maassql's Homepage  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 03/12/2008 :  15:00:21  Show Profile  Visit spirit1's Homepage  Reply with Quote
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 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Edited by - spirit1 on 03/12/2008 15:23:01
Go to Top of Page

maassql
Starting Member

13 Posts

Posted - 03/12/2008 :  15:43:30  Show Profile  Visit maassql's Homepage  Reply with Quote
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 shrinkfile

Anything will give up its secrets if you love it enough. -- George Washington Carver

Edited by - maassql on 03/12/2008 15:43:59
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 03/12/2008 :  15:49:09  Show Profile  Visit spirit1's Homepage  Reply with Quote
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 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 03/12/2008 :  22:55:14  Show Profile  Reply with Quote
>> original size is not in sys.files, sys.database_files, or sys.master_files.

Did you check sys.sysfiles?
Go to Top of Page

maassql
Starting Member

13 Posts

Posted - 03/13/2008 :  11:55:03  Show Profile  Visit maassql's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000