Steve Fenton

SQL Server: Get the size of all databases on a server

The following query will get you the database size and log file size for all databases on a SQL Server instance.

WITH mf AS
(
    SELECT
        database_id,
        [type],
        CAST((size * 8.0 / 1024) AS INT) AS size
    FROM
        sys.master_files
)
SELECT 
    [name] AS DatabaseName,
    (
        SELECT
            SUM(size)
        FROM
            mf
        WHERE
            [type] = 0
        AND
            mf.database_id = db.database_id
    ) DataFileSizeMB,
    (
        SELECT
            SUM(size)
        FROM
            mf
        WHERE
            [type] = 1
        AND
            mf.database_id = db.database_id
    ) LogFileSizeMB
FROM
    sys.databases db
ORDER BY
    DataFileSizeMB DESC