T-SQL query to report disk space allocation and used space for auto-grow SAN databases

Question

We are using 6 databases in total for an application, and we can only share 4TB of space for all 6 databases (via SAN storage). I'd like to write a query (report) for a single database indicating the the "Currently allocated space" and "Available free space" attributes under the Tasks > Shrink > Database option in SQL Server Management Studio. Then I'd like to convert those numbers to TB and total each database to get a rough estimate of how much space we have left. Are these fields that can be accessed via T-SQL query? If so, what would the query look like?

11/30/2012 - 5:13pm update:

note: it says "HIMC_RDC_LZ.mdf" and "HIMC_RDC_LZ.log" ... our database was renamed, hence the inconsistency

---

This query ( EXEC sp_spaceused ) gives two result sets. the first result set matches the "Shrink" screen shot.

Output from the Shrink dialog:

Here's the output from Aaron's answer:

Here's the output from Mike's answer:

Answer 1

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;

Answer 2

Aaron's query is good, but as an alternative, I use this query from Glenn Berry's DMV Queries(you would need to change the math for TBs):

SELECT f.name AS [File Name] 
, f.physical_name AS [Physical Name]
, CAST((f.size/128.0) AS decimal(10,2)) AS [Total Size in MB]
, CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS decimal(10,2)) AS [Available Space In MB]
, [file_id]
, fg.name AS [Filegroup Name]
FROM sys.database_files AS f WITH (NOLOCK)
LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK)
ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);

Answer 3

Here's some SQL that might work for you.

Create Table #dbInfo (dId smallint, dbName sysname, gId smallint NULL, segName varchar(256) NULL, 
       filName varchar(520) NULL, sizeMg decimal(10,2) null, 
       usedMg decimal(10,2) null, freeMg decimal(10,2) null, 
       pcntUsed decimal(10,2) null, pcntFree decimal(10,2) null)
Declare @sSql varchar(1000)
Set @sSql = 'Use [?];
Insert #dbInfo (dId, dbName, gid, segName, filName, sizeMg, usedMg)
Select db_id(), db_name(), groupid, rtrim(name), filename, Cast(size/128.0 As Decimal(10,2)), 
Cast(Fileproperty(name, ''SpaceUsed'')/128.0 As Decimal(10,2))
From dbo.sysfiles Order By groupId Desc;'
Exec sp_MSforeachdb @sSql
Update #dbInfo Set
freeMg = sizeMg - usedMg,
pcntUsed = (usedMg/sizeMg)*100,
pcntFree = ((sizeMg-usedMg)/sizeMg)*100

select * from #dbInfo compute sum(sizeMG),  sum(FreeMg) 
drop table #dbInfo

It's old SQL but still works. One of these days I'm going to rewrite it to use the sys.database_files like the answer from Aaron.