主页 / dba / 问题 / 194192
Accepted
Edgar Allan Bayron
Asked: 2017-12-30 08:01:42 +0800 CST

文件属性 - SPACEUSED

  • 772

我用SELECT FILEPROPERTY('file_name', 'SpaceUsed')了，它返回NULL，这是什么意思？

我在正确的数据库中正确使用它。

我不能假设这NULL意味着空，因为：

我尝试创建一个新数据库（test2），然后查询SELECT FILEPROPERTY('test2', 'SpaceUsed'). 它已经具有价值。

sql-server filegroups

1 个回答

  1. Best Answer

    FILEPROPERTY 需要一个文件（不是文件组）。确保您传入参数的名称位于其中之一：

    从 sys.database_files 中选择名称

    此查询将为您提供有关所选数据库的数据文件的信息：

    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]

    谢谢

    • 3

相关问题