I need to get a list of database sizes for a SQL server migration project. The SQL Server server we're migrating from has three installed instances, one default and and two named. It's running SQL Server 2008 R2, on Server 2008 R2, if it matters.
To get this list, I dusted off the old T-SQL "script" I used at my last position as the defacto/accidental DBA, and it works as expected on the default instance, but returns no data on either of the named instances.
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 GROUP BY GROUPING SETS ( (DB_NAME(database_id), Type_Desc), (DB_NAME(database_id)) ) ORDER BY DB_NAME(database_id), Type_Desc DESC GO
Can someone tell me why (and how to fix it), or suggest a better solution?
(It's gotta be the
FROM sys.master_files bit, because what else could it be, but I'm not sure what I should be using instead on a named instance.)
EDIT: Below is what I'm seeing on my named instances, anyway... so if this code does/should work on named instances, thoughts on why it doesn't work on these would be much appreciated.