-1

I used SELECT FILEPROPERTY('file_name', 'SpaceUsed'), and it returns NULL, what does it mean?

I am correctly using it in the correct database.

I cannot assume that NULL means empty since:

I tried to create a new database (test2), then query SELECT FILEPROPERTY('test2', 'SpaceUsed'). It already has a value.

  • According to the documentation, you need to specify a filename not a filegroup name: docs.microsoft.com/en-us/sql/t-sql/functions/… – Sean says Remove Sara Chipps Dec 29 '17 at 16:04
  • @SeanGallardy-Microsoft, done testing. I will revise my question. What does it mean when some of the filename returns NULL in FILEPROPERTY..spaceused? Some are not. – Edgar Allan Bayron Dec 29 '17 at 16:09
  • Hi @SeanGallardy-Microsoft, I revised my question. I am now using file name. But I wonder why on the production, there are some that returns NULL. Tried in my local environment, even a newly created database returns a value. – Edgar Allan Bayron Dec 29 '17 at 16:14
  • null is returned when the database file provided does not exist. – Dominique Boucher Dec 29 '17 at 16:20
2

The FILEPROPERTY is expecting a file (not a filegroup). Make sure the the name you pass in parameter is on one of those :

select name from sys.database_files

This query will give you information about the data files of the selected database:

With A as 
(SELECT db_name() as [Database],fg.name as [FilegroupName],  RTRIM(f.name) AS [Segment Name], f.groupid AS [Group Id], f.filename AS [File Name], 
CAST(f.size/128.0 AS DECIMAL(10,2)) AS [Size in MB],CAST(FILEPROPERTY(f.name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used],
CAST(size/128.0-(FILEPROPERTY(f.name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space],
CAST((CAST(FILEPROPERTY(f.name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(f.size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used] 
FROM sysfiles f join sys.filegroups fg on f.groupid=fg.data_space_id ) 
select 1 as [order], * from A union  select 2 as [order], REPLICATE('-',10), REPLICATE('-',10), REPLICATE('-',10), 0, REPLICATE('-',50),0,0,0,0 
union 
select 3 as [order], [Database],[FilegroupName],  '', [Group Id], '', sum([Size in MB]), sum([Space Used]), sum([Available Space]), Cast(sum([Space Used])/sum([Size in MB])*100 AS DECIMAL(10,2)) 
from A 
group by [Database],[FilegroupName], [Group Id] 
ORDER BY [order], [Segment Name]

Thanks

  • Thanks, I revised my question. I am now using file name. But I wonder why on the production, there are some that returns NULL. Tried in my local environment, even a newly created database returns a value. – Edgar Allan Bayron Dec 29 '17 at 16:15
  • pretty hard to tell without any information regarding those prod instances :( – Dominique Boucher Dec 29 '17 at 16:16
  • 1
    run the "select name from sys.database_files" on the databases that returns null... The database file may not be the same as the database name. – Dominique Boucher Dec 29 '17 at 16:17
  • thanks for the tip. Because of your query I found the cause. I forgot to mention that I am using name in sys.master_files for the parameter in FILEPROPERTY. That name was different on what was returned in sysfiles. When in SSMS, it's the "Logical Name" that should be used instead. – Edgar Allan Bayron Dec 29 '17 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.