Tuesday, January 10, 2012

SQL Server: Get All Databases Size

To get recent size of all databases on an instance, I have found following simple query very useful. 


SELECT  d.name,
        ROUND(SUM(mf.size) * 8 / 1024, 0) 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
GROUP BY d.name
ORDER BY d.name

6 comments:

  1. In my SQLS2005 I had to cut off "(MBs)" and then it worked. Thanks

    ReplyDelete
    Replies
    1. ROUND(...) AS [Size_MBs]

      Delete
  2. Great stuff!!

    swmrxy@yahoo.com

    ReplyDelete
  3. How about just running "exec sp_helpdb"?

    ReplyDelete

All suggestions are welcome