SQL Server 2008 How to query all databases sizes

Question

I have MS SQL 2008 R2  500 databases  What is the most efficient  easiest and  modern  way to query all databases sizes   The output should have columns    DatabaseName DataFilesSize LogFilesSize

User · Answer

Here s a simple  quick and reliable query that will give all database and log file names  sizes and also database statuses  e g  ONLINE  in a nice  easy to read output   SELECT     D name      F Name AS FileType      F physical name AS PhysicalFile      F state desc AS OnlineStatus      CAST F size AS bigint    8 1024 AS SizeInBytes      CAST  F size 8 0  1024 1024 AS decimal 18 3   AS SizeInGB FROM      sys master files F     INNER JOIN sys databases D ON D database id   F database id ORDER BY SizeInBytes desc

User · Answer

IF OBJECT ID  tempdb dbo  space   IS NOT NULL     DROP TABLE  space  CREATE TABLE  space         database id INT PRIMARY KEY       data used size DECIMAL 18 2        log used size DECIMAL 18 2     DECLARE  SQL NVARCHAR MAX   SELECT  SQL   STUFF       SELECT       USE      d name          INSERT INTO  space  database id  data used size  log used size      SELECT           DB ID             SUM CASE WHEN  type    0 THEN space used END            SUM CASE WHEN  type    1 THEN space used END      FROM           SELECT s  type   space used   SUM FILEPROPERTY s name    SpaceUsed      8    1024          FROM sys database files s         GROUP BY s  type        t       FROM sys databases d     WHERE d  state    0     FOR XML PATH      TYPE  value       NVARCHAR MAX     1  2       EXEC sys sp executesql  SQL  SELECT       d database id       d name       d state desc       d recovery model desc       t total size       t data size       s data used size       t log size       s log used size FROM       SELECT           database id           log size   CAST SUM CASE WHEN  type    1 THEN size END    8    1024 AS DECIMAL 18 2             data size   CAST SUM CASE WHEN  type    0 THEN size END    8    1024 AS DECIMAL 18 2             total size   CAST SUM size    8    1024 AS DECIMAL 18 2       FROM sys master files     GROUP BY database id   t JOIN sys databases d ON d database id   t database id LEFT JOIN  space s ON d database id   s database id ORDER BY t total size DESC

User · Answer

sometimes SECURITY issues prevent from asking for all the db s and you need to query one by one with the db prefix  for those cases i created this dynamic query  go declare  Results table   Name  nvarchar max    DataFileSizeMB  int   LogFileSizeMB  int    declare  QaQuery nvarchar max  declare  name nvarchar max   declare MY CURSOR cursor    local static read only forward only for  select name from master dbo sysdatabases where name not in   master    tempdb    model    msdb    rdsadmin     open MY CURSOR fetch next from MY CURSOR into  name while   FETCH STATUS   0 begin      if len  name  gt 0      begin         print  name     Column Exist          set  QaQuery   N select                                   name     as Name                              sum case when type   0 then size else 0 end  as DataFileSizeMB                              sum case when type   1 then size else 0 end  as LogFileSizeMB                         from     name    sys database files                         group by replace name     log                     insert  Results exec sp executesql  QaQuery      end   fetch next from MY CURSOR into  name end close MY CURSOR deallocate MY CURSOR  select   from  Results order by DataFileSizeMB desc go

User · Answer

please find more deatils or download the script from below link     https   gallery technet microsoft com SIZE-OF-ALL-DATABASES-IN-0337f6d5 content   DECLARE  spacetable table     database name varchar 50     total size data int   space util data int   space data left int   percent fill data float   total size data log int   space util log int   space log left int   percent fill log char 50     total db size  int    total size used  int    total size left  int     insert into   spacetable  EXECUTE master sys sp MSforeachdb  USE       select x  DATABASE NAME  x  total size data  x  space util  x  total size data -x  space util   space left data    x  percent fill  y  total size log  y  space util    y  total size log -y  space util   space left log  y  percent fill    y  total size log  x  total size data    total db size     x  space util  y  space util    total size used      y  total size log  x  total size data  - y  space util  x  space util     total size left     from  select DB NAME     DATABASE NAME     sum size 8 1024    total size data   sum FILEPROPERTY name   SpaceUsed    8 1024    space util     case when sum size 8 1024  0 then   less than 1  used   else  substring cast  sum FILEPROPERTY name   SpaceUsed     1 0 100 sum size   as CHAR 50   1 6  end   percent fill    from sys master files where database id DB ID DB NAME     and  type 0  group by type desc    as x     select   sum size 8 1024    total size log   sum FILEPROPERTY name   SpaceUsed    8 1024    space util     case when sum size 8 1024  0 then   less than 1  used   else  substring cast  sum FILEPROPERTY name   SpaceUsed     1 0 100 sum size   as CHAR 50   1 6  end   percent fill    from sys master files where database id DB ID DB NAME     and  type 1  group by type desc   y   select   from  spacetable  order by database name

User · Answer

I don t know exactly what you mean by efficiency but this is straightforward and it works for me     SELECT     DB NAME db database id  DatabaseName       CAST mfrows RowSize AS FLOAT  8  1024 RowSizeMB       CAST mflog LogSize AS FLOAT  8  1024 LogSizeMB       CAST mfstream StreamSize AS FLOAT  8  1024 StreamSizeMB       CAST mftext TextIndexSize AS FLOAT  8  1024 TextIndexSizeMB FROM sys databases db     LEFT JOIN  SELECT database id  SUM size  RowSize FROM sys master files WHERE type   0 GROUP BY database id  type  mfrows ON mfrows database id   db database id     LEFT JOIN  SELECT database id  SUM size  LogSize FROM sys master files WHERE type   1 GROUP BY database id  type  mflog ON mflog database id   db database id     LEFT JOIN  SELECT database id  SUM size  StreamSize FROM sys master files WHERE type   2 GROUP BY database id  type  mfstream ON mfstream database id   db database id     LEFT JOIN  SELECT database id  SUM size  TextIndexSize FROM sys master files WHERE type   4 GROUP BY database id  type  mftext ON mftext database id   db database id   With results like   DatabaseName  RowSizeMB LogSizeMB StreamSizeMB TextIndexSizeMB ------------- --------- --------- ------------ --------------- master        4         1 25      NULL         NULL model         2 25      0 75      NULL         NULL msdb          14 75     8 1875    NULL         NULL tempdb        8         0 5       NULL         NULL   Note  was inspired by this article

User · Answer

The following code worked for me very well       SELECT         D name As DbName          F Name AS FullDbName          CASE WHEN F type desc  ROWS  THEN  mdf  ELSE  ldf  END AS FileType          F physical name AS PhysicalFile          CONVERT DATE D create date  AS CreationDate          F state desc AS OnlineStatus          CAST  F size 8  1024 AS VARCHAR 26       MB  AS FileSize MB          CAST F size 8 AS VARCHAR 32       Bytes  AS FileSize Bytes          CAST CAST ROUND  F size 8   1024 0 1024 0  0  AS INT  AS VARCHAR 32       GB  AS FileSize GB      FROM          sys master files F         INNER JOIN sys databases D ON D database id   F database id      ORDER BY          D name

User · Answer

I recently came across this page looking for something like this  Just in case someone comes across this and is interested in only the core user databases  you can use something like this which excludes Master  msdb     SELECT DB NAME db database id  DatabaseName   CAST mfrows RowSize AS FLOAT  8  1024 RowSizeMB   CAST mflog LogSize AS FLOAT  8  1024 LogSizeMB FROM sys databases db LEFT JOIN  SELECT database id  SUM size  RowSize FROM sys master files WHERE type   0 GROUP BY database id  type  mfrows ON mfrows database id   db database id LEFT JOIN  SELECT database id  SUM size  LogSize FROM sys master files WHERE type   1 GROUP BY database id  type  mflog ON mflog database id   db database id where DB NAME db database id  not like  master  and DB NAME db database id  not like  msdb  and DB NAME db database id  not like  model  and DB NAME db database id  not like  tempdb  and DB NAME db database id  not like  Northwind  and DB NAME db database id  not like  ReportServer  order by DB NAME db database id

User · Answer

Not to steal your answer and adapt it for points or anything  but here is another factorization   select d name       sum m0 size 8 0 1024  data file size mb       sum m1 size 8 0 1024  log file size mb  from sys databases d inner join sys master files m0 on m0 database id   d database id inner join sys master files m1 on m1 database id   d database id where m0 type   0 and m1 type   1 group by d name  d database id order by d database id

User · Answer

Simplified  amp  Improved version   SELECT     D name      CAST SUM F size  AS bigint    8 1024 AS SizeInBytes      CAST SUM F size 8 0  1024 1024 AS decimal 18 3   AS SizeInGB FROM      sys master files F     INNER JOIN sys databases D ON D database id   F database id GROUP BY D name ORDER BY SizeInBytes desc

User · Answer

All seem overly complicated  Or am I missing something   Surely all you need is something like   select d name  case when m type   0 then  Data  else  Log  end   m size   8   1024 from sys master files m JOIN sys databases d ON d database id   m database id   or if you don t want the log   select d name  m size   8   1024 from sys master files m JOIN sys databases d ON d database id   m database id and m type  0

User · Answer

with fs as       select database id  type  size   8 0   1024 size     from sys master files   select      name       select sum size  from fs where type   0 and fs database id   db database id  DataFileSizeMB       select sum size  from fs where type   1 and fs database id   db database id  LogFileSizeMB from sys databases db

User · Answer

SELECT     DB name      SUM CASE WHEN type   0 THEN MF size   8   1024 ELSE 0 END  AS DataFileSizeMB      SUM CASE WHEN type   1 THEN MF size   8   1024 ELSE 0 END  AS LogFileSizeMB FROM     sys master files MF     JOIN sys databases DB ON DB database id   MF database id GROUP BY DB name ORDER BY DataFileSizeMB DESC

User · Answer

A better and quite simpler one   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   It will give you size of Data File s  and Log File s  separately like below  DatabaseName    Type            Size in MB ------------------------------------------- FMS             Data File s     23 00 FMS             Log File s      1 50 PointOfSale     Data File s     4 00 PointOfSale     Log File s      1 25 Union2          Data File s     336 00 Union2          Log File s      1191 13 SurveyProject   Data File s     4 00 SurveyProject   Log File s      1 00

User · Answer

All this examples work fine on most of my database servers  However if you have 1 server with multiple instances and all those servers those query s give you no result  For instance the first query gives a result like  name    DataFileSizeMB  LogFileSizeMB master  NULL    NULL tempdb  NULL    NULL model   NULL    NULL msdb    NULL    NULL  So the databases are selected from sys databases  however the table sys master files seems to be empty or gives no result  Even a simple query as select   from sys master files has a result of 0 records  You don t receive any errors but no records are found  On my servers with only 1 database instance this works fine

User · Answer

with Total Database size ordered Desc  SELECT      DB NAME db database id  DatabaseName        CAST mfrows RowSize AS FLOAT  8  1024 RowSizeMB        CAST mflog LogSize AS FLOAT  8  1024 LogSizeMB    CAST mfrows RowSize AS FLOAT  8  1024 1024  CAST mflog LogSize AS FLOAT  8  1024 1024 DBSizeG   CAST mfstream StreamSize AS FLOAT  8  1024 StreamSizeMB        CAST mftext TextIndexSize AS FLOAT  8  1024 TextIndexSizeMB  FROM sys databases db      LEFT JOIN  SELECT database id                     SUM size  RowSize              FROM sys master files              WHERE type   0              GROUP BY database id  type  mfrows      ON mfrows database id   db database id      LEFT JOIN  SELECT database id                     SUM size  LogSize              FROM sys master files              WHERE type   1              GROUP BY database id  type  mflog      ON mflog database id   db database id      LEFT JOIN  SELECT database id                     SUM size  StreamSize                    FROM sys master files                    WHERE type   2                    GROUP BY database id  type  mfstream      ON mfstream database id   db database id      LEFT JOIN  SELECT database id                     SUM size  TextIndexSize                    FROM sys master files                    WHERE type   4                    GROUP BY database id  type  mftext      ON mftext database id   db database id         ORDER BY 4 DESC

[sql] SQL Server 2008: How to query all databases sizes?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008-r2