Tracing the query used by ssms
One way to do this is by taking the profiler's 'tuning' template and then tracing your login
This query pops up
SELECT
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
(SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS df WHERE df.type = 2 )) 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
The query gets a DbSize and SpaceUsed column from some DMV's.
The DbSize column
A straightforward query, get the size where the file type
= 0 = Rows= 2 = FILESTREAM= 4 = Full-text
This query gets all file sizes except the log size.
I'm assuming this is added later as it is added to the total size in the properties pane.
SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize]
The SpaceUsed column
A bit bigger, first get the sum of total pages from the sys.allocation_units DMV
...SUM(a.total_pages) + ...
FROM
sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
...
And then add any filestream data to this number (if none then add 0 ):
...(SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS df WHERE df.type = 2 )
FROM
sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id ...
The total query
Running the total query gives me:
DbSize SpaceUsed
2557952 886791
Both the DBSize and SpaceUsed column is the size in 8KB pages.
What can I do to get exactly the same number the properties dialog
shows for Space Available?
If I transform the query to be divided by /128.0 and subtract SpaceUsed from DBSize like your query
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
I can replicate the properties view of the space available:
FreeSpace
13055,945313
Still need to apply rounding with 2 digits after the comma
where your query gives me a lower amount on my database
FreeSpaceMB
13055.687500
Trying to find a why
Assumptions
We established that the DBSize column is the same for both queries, the difference should be between the total_pages column from the sys.allocation_units dmv and the FILEPROPERTY function
FILEPROPERTY(name, 'SpaceUsed') AS INT).
on the FILEPROPERTY documentation:
Number of pages allocated in the file
Source
Where total_pages =
Total number of pages allocated or reserved by this allocation unit.
Source
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
Using sp_spaceused and @updateusage to update the space usage info (DBCC UPDATEUSAGE)
exec sp_spaceused
The sp_spaceused procedure gives the same result as the SSMS Properties window and the sys.allocation_units query.
unallocated space
13055.95 MB
But when running sp_spaceused with @updateusage = 'true'
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
EXEC sys.sp_spaceused @updateusage = 'true';
The values are updated:
unallocated space
13056.07 MB
The changed SSMS query that uses sys.allocation_units also shows this updated value:
FreeSpace
13056,070313
While the FILEPROPERTY query did not change!
SELECT size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;
FreeSpaceMB
13055.687500
