Getting database sizes on an MSSQL Server named instance

Question

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.

score 10 · Accepted Answer · edited Apr 13 '17 at 12:42

If you just need the size of the entire database then below script will help you :

SELECT  @@servername as server_name,
        d.name,
        CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00)) Size_MBs
FROM    sys.master_files mf
        INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE   d.database_id > 4 -- Skip system databases
AND     d.state_desc = 'ONLINE'-- ONLY get size for ONLINE database.
GROUP BY d.name
ORDER BY d.name

If you want to automate your migration of databases from one server to another, I highly recommend using sqlmigration - a powershell script that will migrate your databases, jobs, logins, etc from one server to another. Also, be aware of the pre and post migration steps if you are moving from one version of sql server to higher (2012 or 2014) version.

Depending on your size of databases and the amount of downtime, there are ways that you can implement to minimize the downtime for your entire migration - e.g. using logshipping or mirroring and then during cutover, just failover to the new server, etc.

Always prepare and test your migration and rollback procedures.

Edit: From your screenshot, it seems that you do not have permissions. Can you just do a select * from sys.master_files

Getting database sizes on an MSSQL Server named instance

1 Answers1