September 30, 2013 at 8:17 am
Can anyone explain the behavior of the query below. I was trying to write a query that can be run on a SQL server that will return the database name, filegroup, logical filename and physical filename for every file on the SQL server with the size of each file and the space available within the file.
the total space in for each file is returned correctly no matter which database the query is run from. The SpaceUsed values are only correct for the specific database that the query is run in.
Is there a different way [different table] to get the "SpaceUsed" value from?
Or does someone have a better way to get this group of information?
Do you spot a mistake in my joins?
Thank you for your help.
SELECT d.name as DatabaseName, fg.groupname as FileGroupName, df.name as LogicalFileName,
CASE df.type WHEN 0 then 'Data' ELSE 'Log' END AS UsageType,
df.size/128.0 as SpaceInMB,
df.size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB,
(df.size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0)/1024 as AvailSpaceInGB
,df.physical_name
FROM ((sys.sysdatabases d join sys.master_files df on d.dbid = df.database_id )
join sys.sysfiles f on df.file_id = f.fileid )
left outer join sys.sysfilegroups fg on f.groupid = fg.groupid
ORDER by d.name, fg.groupname, df.file_id;
September 30, 2013 at 8:43 am
look at this useful link
September 30, 2013 at 8:43 am
According to TechNet, FILEPROPERTY
"returns the specified file name property value when a file name in the current database and a property name are specified. Returns NULL for files that are not in the current database."
http://technet.microsoft.com/en-us/library/ms188401%28v=sql.105%29.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply