Return to Answer

We are getting the sum of all these allocation units. My guess would be that the difference is based on the file having more overhead pages not directly in the allocation units dmv.

Guess

We are getting the sum of all these allocation units. My guess would be that the difference is based on the file having more overhead pages not directly in the allocation units dmv, such as File header, PFS, GAM, SGAM, DCM, BCM.

More on these here

SELECT
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) )/ 128.0 -
(SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS df WHERE df.type = 2 ))/ 128.0 AS [SpaceUsed]
FROM
sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id

SpaceUsed
13055,945313

But that still does not exactly explain why FILEPROPERTY(name, 'SpaceUsed') AS INT) would return more.

Execution plan

When we look at what the function is doing in the execution plan, we do find a convert implicit on the fileproperty calculation:

[Expr1022]-CONVERT_IMPLICIT(numeric(10,0),fileproperty([test].[sys].[sysprufiles].[lname] as [f].[lname],'SpaceUsed'),0)/(128.0)

We can open a DAC connection and try to query this 'hidden' system table.

Use Test
Go
SELECT * FROM sys.sysprufiles

SELECT fileproperty([lname] ,'SpaceUsed')
FROM [test].[sys].[sysprufiles]

But it does not really make a difference.

Hopefully somebody else finds an exact why.

SpaceUsed
13056,070313

SELECT
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) )/ 128.0 -
(SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS df WHERE df.type = 2 ))/ 128.0 AS [FreeSpace]
FROM
sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id

FreeSpace
13055,945313

We are getting the sum of all these allocation units. My guess would be that the difference is based on the file having more overhead pages not directly in the allocation units dmv.

FreeSpace
13056,070313

Trying to find a why

on the fileproperty documentation:

Where total_pages =

When we look at what the function is doing in the execution plan, we do find a convert implicit on the fileproperty calculation:

sp_spaceused and @updateusage

Trying to find a why

Assumptions

on the FILEPROPERTY documentation:

Source

Where total_pages =

Source

Execution plan

When we look at what the function is doing in the execution plan, we do find a convert implicit on the fileproperty calculation:

Using sp_spaceused and @updateusage to update the space usage info (DBCC UPDATEUSAGE)

Indicates DBCC UPDATEUSAGE should be run to update space usage information. When objname is not specified, the statement is run on the whole database; Source