SQL Server Database Sizes with Drive Information

By Tom Nonmacher

Notes from the Field:

When managing storage across a SQL Server environment, it’s not enough to just know how large each database is, you must need to understand which drives are hosting those files. This is especially important when balancing I/O, planning for growth, or diagnosing space-related issues. 

Here’s a query that gives you exactly that: a breakdown of each database's size in gigabytes, separated into data (ROWS) and log (LOG), along with the physical drive path for each file.:


-- Returns database sizes and drive info in GB

SELECT 
    db.name AS DatabaseName,
    LEFT(mf.physical_name, 2) AS Drive,
    mf.type_desc AS FileType,
    SUM(mf.size) * 8.0 / 1024 / 1024 AS SizeGB
FROM 
    sys.master_files mf
JOIN 
    sys.databases db ON db.database_id = mf.database_id
WHERE 
    db.name NOT IN ('master', 'model', 'msdb', 'tempdb')
GROUP BY 
    db.name, LEFT(mf.physical_name, 2), mf.type_desc
ORDER BY 
    db.name, Drive;

Output Columns Explained

DatabaseName
The name of the database.

Drive
The drive letter (e.g., C:, D:) extracted from the file path. This helps you see which physical volumes are being used.

FileType
Either ROWS (data files) or LOG (transaction log files).

SizeGB
The total size of the file type on that drive for that database, in gigabytes.

Why This Matters

Having a quick view of database sizes is a critical part of daily monitoring and long-term capacity planning. This query helps you:

  • Identify large or rapidly growing databases

  • Plan for storage upgrades or migrations

  • Spot imbalances in log file growth

  • Exclude system databases from your size analysis

This is a go-to tool in the DBA toolbox, its simple and available on any SQL Server instance.

The Data Whisperer
SQLSupport.org 

Check out the latest articles from all our sites:




7A50A8
Please enter the code from the image above in the box below.