SQL Server – Get Size of All Databases in MB and GB

9 Comments

1. 

   DBA Doug

   31/12/2019 at 12:34 am

   Most excellent simple grouping of database files in calculation. Like it.

   Reply
2. 

   Noor

   08/04/2020 at 1:40 pm

   Getting error like ..

   Msg 8115, Level 16, State 2, Line 1
   Arithmetic overflow error converting expression to data type int.

   Reply
   • 

     Ricky

     17/04/2020 at 2:45 am

     Hi Noor,

     Please use this.

     SELECT d.NAME
     ,ROUND(SUM(CAST(mf.size AS bigint)) * 8 / 1024, 0) Size_MBs
     ,(SUM(CAST(mf.size AS bigint)) * 8 / 1024) / 1024 AS Size_GBs
     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

     I’m going to replace the script in my blog post.

     Thank you!

     Reply
3. 

   Vaibhav

   30/05/2020 at 9:14 am

   What if I want the total space of all databases?

   Reply
   • 

     Enrique

     30/06/2020 at 9:33 pm

     Hi Vaibhav,

     Sorry for the late reply. You can try the script below.

     SELECT a.name,
     [FILE_SIZE_GB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2))/1024,
     [SPACE_USED_GB] = CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.name, ‘SpaceUsed’) / 128.000, 2))/1024.0,
     [FREE_SPACE_GB] = CONVERT(DECIMAL(12, 2), ROUND((a.size – FILEPROPERTY(a.name, ‘SpaceUsed’)) / 128.000, 2))/1024,
     [CURRENT_TIME] = GETDATE(),
     a.filename
     FROM dbo.sysfiles a
     –where CONVERT(DECIMAL(12, 2), ROUND((a.size – FILEPROPERTY(a.name, ‘SpaceUsed’)) / 128.00, 2))/1024.0>20
     order by [FREE_SPACE_GB] desc

     Reply
     • 

       Helena

       15/01/2021 at 4:53 am

       Hi this script / select looks fantastic i had one similar for sybase, in sqlserver on linux when I execute it gives me error messages

       cat h.sql
       SELECT a.name,
       [FILE_SIZE_GB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2))/1024,
       [SPACE_USED_GB] = CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.name, SpaceUsed) / 128.000, 2))/1024.0,
       [FREE_SPACE_GB] = CONVERT(DECIMAL(12, 2), ROUND((a.size- FILEPROPERTY(a.name, SpaceUsed)) / 128.000, 2))/1024, [CURRENT_TIME] = GETDATE(), a.filename from dbo.sysfiles a where CONVERT(DECIMAL(12, 2), ROUND((a.size FILEPROPERTY(a.name, SpaceUsed)) / 128.00, 2))/1024.0>20 order by [FREE_SPACE_GB] desc
       go
       mssql@test /home/mssql/ sql -i h.sql
       Msg 102, Level 15, State 1, Server test , Line 4
       Incorrect syntax near ‘FILEPROPERTY’.

       Reply
4. 

   Allen

   19/09/2020 at 4:00 am

   Hi,
   What permission do I need to check the size of all databases in an instance without having access to any of the databases? Will VIEW_ANY_DEFINITION at the server level help? Please let me know. thanks

   Reply
   • 

     Enrique

     28/09/2020 at 10:19 am

     Hi Allen, you can execute this script without having access to user databases. Please try it. Thanks.

     Reply
5. 

   Michael Karpenko

   05/11/2021 at 1:05 am

   Just in case if script above is not showing FileStream based database size properly

   SELECT
   (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
   (SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS df WHERE df.type = 2 )) AS [SpaceUsed]
   FROM
   sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id

   Reply