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
Best Answer
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.
For example, the following query will work and won't return nulls.