Here is the query Management Studio uses to populate those numbers:
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) AS [SpaceUsed],
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df
WHERE df.type in (1, 3)) AS [LogSize]
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
You need to perform math here just like Management Studio does in order to get the same numbers. Also, the left join to sys.internal_tables seems superfluous at best. So adjusting that query to match your ideal output:
SELECT
(SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
FROM sys.database_files AS df
WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
CONVERT(DECIMAL(18,2), SUM(a.total_pages)*8/1024.0) AS [SpaceUsed],
(SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
FROM sys.database_files AS df
WHERE df.type in (1, 3)) AS [LogSize]
FROM sys.partitions p join sys.allocation_units a
on p.partition_id = a.container_id;
With this update, assuming your database doesn't change in the meantime, it should yield:
753475.94 744030.07 2900.00
Doing some simple math, and isolating just the three numbers you want:
;WITH t(s) AS
(
SELECT CONVERT(DECIMAL(18,2), SUM(size)*8/1024.0)
FROM sys.database_files
WHERE [type] % 2 = 0
),
d(s) AS
(
SELECT CONVERT(DECIMAL(18,2), SUM(total_pages)*8/1024.0)
FROM sys.partitions AS p
INNER JOIN sys.allocation_units AS a
ON p.[partition_id] = a.container_id
)
SELECT
Allocated_Space = t.s,
Available_Space = t.s - d.s,
[Available_%] = CONVERT(DECIMAL(5,2), (t.s - d.s)*100.0/t.s)
FROM t CROSS APPLY d;
Best Answer
Think of your database like a bucket.
The ALLOCATED space is how big the bucket is, i.e. how much data it can hold.
The USED space is how much water/data is actually inside the bucket.
When the water gets to the top of the bucket, you need to make the bucket bigger by allocating more space.