To get database size information we can use sys.master_files catalog view. This view contains a row per file of a database for all databases.

The columns of interest for retrieving database size information are:

Using this information we can retrieve database sizes using 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

Result Set:

 Database Name        Type                 Size in MB

AdventureWorks2012   Data File(s)         189.00

AdventureWorks2012   Log File(s)          0.75

AdventureWorks2012   NULL                 189.75

Credit               Data File(s)         170.94

Credit               Log File(s)          10.00

Credit               NULL                 180.94

master              Data File(s)          4.00

master              Log File(s)           0.75

master              NULL                  4.75

model                Data File(s)         3.06

model                Log File(s)          0.75

model                NULL                 3.81

msdb                 Data File(s)         16.69

msdb                 Log File(s)          19.63

msdb                 NULL                 36.31

Northwind            Data File(s)         4.25

Northwind            Log File(s)          3.06

Northwind            NULL                 7.31

pubs                 Data File(s)         3.25

pubs                 Log File(s)          3.06

pubs                 NULL                 6.31

SqlAndMe             Data File(s)         137.88

SqlAndMe             Log File(s)          19.13

SqlAndMe             NULL                 157.00

tempdb              Data File(s)          8.00

tempdb              Log File(s)           0.50

tempdb              NULL                  8.50

(27 row(s) affected)

The above query gets sizes for Data Files and Log Files and displays a total using GROUPING SETS.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Filed under: Catalog Views, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012