how to get databases size in mssql

 just to paste this code, you will get all detail :-

    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

 

Just see the header

DbName

FullDbName

FileType

PhysicalFile

CreationDate

OnlineStatus

FileSize_MB

FileSize_Bytes

FileSize_GB

Simplified Code :-

 

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