How To Get The Size of All Databases on MS SQL Server

How To Get The Size of All Databases on SQL Server

The size of a database is frequently estimated by SQL database administrators. In SQL Server Management Studio (SSMS), you can easily determine the SQL Server database size if you only need to examine one database: Right-click the database and then click Reports -> Standard Reports -> Disk Usage.   As an alternative, you can calculate database size using stored procedures like exec sp_spaceused.

Unfortunately, just one database's size will be displayed using either of these approaches. T-SQL queries, which are more complicated and demand more advanced scripting skills, are required to execute all databases on SQL Server.

Get the size of Data File (MB), Log File (MB) and Total Size of Database in GB on MSSQL Server.

 

SELECT DBName,

DataFile                        AS DataFileSizeInMB,

LogFile                         AS LogFileInMB,

( DataFile + LogFile ) / 1024.0 AS DataBaseSizeInGB

FROM   (SELECT DB_NAME(Database_id) AS DBName,

size * 8.0 / 1024    AS SizeInMB,

CASE

WHEN TYPE = 0 THEN 'DataFile'

ELSE 'LogFile'

END                  AS FileType

FROM   sys.master_files) D

PIVOT ( MAX(SizeInMB)

FOR FileType IN (DataFile, LogFile)) pvt