Home > Catalog Views, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012 > SQL Server – How to get sizes of all databases on a server

SQL Server – How to get sizes of all databases on a server

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:

Column Name Description
database_id ID of the database to which the file belongs to
type_desc Description of file type. It can be ROWS, LOG, FILESTREAM or FULLTEXT
size File size in number of 8KB pages

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

Advertisements
  1. GDubyah
    July 23, 2013 at 12:57 pm

    Hi, thanks for that but what is the NULL type? There is space associated with it.

  2. August 1, 2013 at 12:38 pm

    @GDubyah, it is the total size, i.e. Data file + Log file

  3. anchita
    August 24, 2013 at 4:46 am

    Good one!

  4. Jim
    September 25, 2013 at 12:46 am

    How would you order by largest size to smallest?

    • Bubba
      October 17, 2013 at 7:50 pm

      Jim, change your order by to be based on size instead of db_name – in other words, learn basic sql

  5. Hernan Rojas
    May 7, 2014 at 1:37 am

    Thank you!! very useful query. I had searched a script like this for months… you did it!!

  6. Karl
    March 27, 2015 at 2:25 pm

    Thank you!! very useful query.
    But something I would like to ask.
    I would like to add Last update time for this selection
    I will show my code below
    part b should be failed as b.LastUserUpdate as left join xxxxxx are failed
    I can output when delete b.LastUserUpdate and part b
    Could you tell me how to solve this problem?

    select a.[Database Name],a.[Type],a.[Size in MB],b.LastUserUpdate
    from
    (SELECT database_id,[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))
    )) as a
    left join
    (select max(last_user_update) as LastUserUpdate,database_id from sys.dm_db_index_usage_stats
    group by database_id ) as b on a.database_id=b.database_id

  1. September 16, 2014 at 8:27 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: