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