SQL Server – Discrepancies in Available Space Between sys.database_files and SSMS

sql serversql-server-2012ssms

I am looking at this for a few minutes now and could not find a thread mentioning this.

I wanted to query currently used space and available space from a database on SQL Server.
The numbers I query should match the numbers I see, when I open the properties dialog on a database in SQL Server Management Studio, like this:

sample database properties showing Size: 17689,00MB and Space Available: 16795,77MB

So I browsed a bit and found that I can query against sys.database_files like so (from here):

SELECT
        DB_NAME() AS DbName, 
        name AS FileName, 
        size/128.0 AS CurrentSizeMB, 
        size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
    FROM sys.database_files;

This gets me this result:

DbName       FileName         CurrentSizeMB   FreeSpaceMB
databasename databasename     17338.000000    16795.312500
databasename databasename_log   351.000000      331.234375

Trying to recognize my numbers from the previous dialog:

  • OK, adding 17338and 351 gets me to 17689 which is exactly the size the dialog lists
  • But for available space I cannot find it, 16795.312500 is off by almost half a MB

What can I do to get exactly the same number the properties dialog shows for Space Available?

Did I use the query/formulae in a wrong way or is this simply not possible at this point due to some rounding (converting pages/extents to MB…)?

Best Answer

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

enter image description here

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
Related Question