Using SQL 2012R2 Enterprise. I am unable to get the Size of the FILESTREAM data using the query below. The FileStreamSizeMB column is always Zero, but the other columns for log and data sizes are ok. The FileStream data directory has 2GB of data on the file system... Everything is working perfect, except I can not calculate size for FILESTREAM. My database is using the following Hard Disks.

D: = data files.
E: = log files.
F: = FILESTREAM data

with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB,
 (select sum(size) from fs where type = 2 and fs.database_id = db.database_id) FileStreamSizeMB
from sys.databases db

• Edited by Russell Mangel Friday, July 24, 2015 4:29 AM

Hi Russel,

Use the below query 

SELECT [Database Name] = DB_NAME(database_id),

       [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'

                     WHEN Type_Desc = 'LOG'  THEN 'Log File(s)'

                     ELSE Type_Desc END,

       [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )

FROM   sys.master_files

Uncomment if you need to query for a particular database

WHERE      database_id = DB_ID(Database Name)

GROUP BY      GROUPING SETS

              (

                     (DB_NAME(database_id), Type_Desc),

                     (DB_NAME(database_id))

              )

ORDER BY      DB_NAME(database_id), Type_Desc DESC

GO

This does not work either, your query also returns 0.0 for FILESTREAM data, same as my original query.

As BOL says, (https://msdn.microsoft.com/en-us/library/ms186782.aspx?f=255&MSPPError=-2147217396) the value of size in master.sys.master_files is zero for FILESTREAM containers.  You have to get that information from the sys.database_files catalog view in each database.  One way to do that would be to run a query against each database, or you can do it in one query by

 declare @sql nvarchar(max)

 select @sql = (Select N' Union All Select DB_Name(' + Cast(database_id As varchar(5)) + N') DbName,
   sum(case when type = 0 then size*8.0/1024 end) DataFileSizeMB, 
   sum(case when type = 1 then size*8.0/1024 end) LogFileSizeMB, sum(case when type = 2 then size*8.0/1024 end) FileStreamSizeMB 
   from ' + QuoteName(name) + N'.sys.database_files' from master.sys.databases
 For XML Path(''),Type)
    .value('text()[1]','nvarchar(max)')
 select @sql = stuff(@sql, 1, 10, '')
 --select @sql
 exec (@sql)

Tom

• Marked as answer by Russell Mangel 1 hour 4 minutes ago

You deserve to be known. Working now, thank you!

Where should I send the keys to your new Lamborghini, hope you like yellow.

-Russ