FILEPROPERTY not working over linked server to Azure SQL Database

Question

FILEPROPERTY (Transact-SQL) is not working with a linked server pointing from a on-Premise SQL 2017 to a Azure SQL Database. I see the following comment in books online:

Returns NULL for files that are not in the current database.

But my linked server has @catalog pointing to a specific database.

Here is my code and I get NULL for spaceUsedMB , freeSpaceMB, and spaceUsedMB columns.

SELECT  
 collectedAt = GetDate(),
 serverName =(SELECT * FROM OPENQUERY (AzureDB_adventureworks, 'SELECT @@SERVERNAME')),
 databaseName=(SELECT * FROM OPENQUERY (AzureDB_adventureworks, 'SELECT DB_NAME()')),
 fileName = LEFT(a.NAME, 64) ,
 a.FILE_ID AS fileId,
 fileSizeMB = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)),
 spaceUsedMB = CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.name,'SpaceUsed')/ 128.000, 2)),
 freeSpaceMB = CONVERT(DECIMAL(12, 2), ROUND(( a.size - FILEPROPERTY(a.name,'SpaceUsed'))/ 128.000, 2)),
 percentFree = CONVERT(DECIMAL(12, 2), (CONVERT(DECIMAL(12, 2), ROUND((a.size - FILEPROPERTY(a.name,'SpaceUsed'))/128.000, 2))*100)/ CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2))),
 a.physical_name 
FROM [AzureDB_adventureworks].[adventureworks].sys.database_files a

I was able to get by with following code but still curious to know why above code did not work.

SELECT * FROM OPENQUERY (AzureDB_adventureworks, 
    'SELECT 
        GETDATE() AS collectedAT,
        @@SERVERNAME AS serverName, 
        DB_NAME() AS databaseName, 
        LEFT(a.name, 64) AS fileName,
        a.file_id AS fileId,
        a.size AS fileSizeMB,
        CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.name,''SpaceUsed'')/ 128.000, 2)) AS spaceUsedMB,
        CONVERT(DECIMAL(12, 2), ROUND(( a.size - FILEPROPERTY(a.name,''SpaceUsed''))/ 128.000, 2)) AS freeSpaceMB,
        CONVERT(DECIMAL(12, 2), (CONVERT(DECIMAL(12, 2), ROUND((a.size - FILEPROPERTY(a.name,''SpaceUsed''))/128.000, 2))*100)/ CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2))) as percentFree,
        a.physical_name AS physicalName 
from adventureworks.sys.database_files a'
) 

If I run the following code against Azure SQL Database I will get correct result for all columns.

SELECT  
 collectedAt = GetDate(),
 serverName = @@SERVERNAME,
 databaseName= DB_NAME(),
 fileName = LEFT(a.NAME, 64) ,
 a.FILE_ID AS fileId,
 fileSizeMB = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)),
 spaceUsedMB = CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.name,'SpaceUsed')/ 128.000, 2)),
 freeSpaceMB = CONVERT(DECIMAL(12, 2), ROUND(( a.size - FILEPROPERTY(a.name,'SpaceUsed'))/ 128.000, 2)),
 percentFree = CONVERT(DECIMAL(12, 2), (CONVERT(DECIMAL(12, 2), ROUND((a.size - FILEPROPERTY(a.name,'SpaceUsed'))/128.000, 2))*100)/ CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2))),
 a.physical_name 
FROM sys.database_files a

Answer 1

Please note that logs are excluded for the purposes of determining database size in Azure SQL Database, that is the reason you receive NULL as result. You should add the following WHERE clause to your query.

WHERE type_desc = 'ROWS';

For example, the following query will work and won't return nulls.

SELECT 
SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) 
AS DatabaseSizeInBytes,
SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.)/1024 /1024
AS DatabaseSizeInMB,
SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.)/1024/1024/1024 
AS DatabaseSizeInGB
FROM sys.database_files
WHERE type_desc = 'ROWS';