Sql server Database list with Size

 

How to List Databases with Their Sizes in SQL Server

If you're managing a SQL Server instance and want to check the size of each database—including both data and log files—you can use the following query. This script retrieves all user databases, calculates the size of their data and log files in megabytes, and displays the results in descending order by data file size.

SQL Query

SELECT
    DB.name,
    SUM(CASE WHEN [type] = 0 THEN MF.size * 8 / 1024 ELSE 0 END) AS DataFileSizeMB,
    SUM(CASE WHEN [type] = 1 THEN MF.size * 8 / 1024 ELSE 0 END) AS LogFileSizeMB
FROM    sys.master_files MF
JOIN sys.databases DB ON DB.database_id = MF.database_id
WHERE DB.source_database_id IS NULL -- Exclude database snapshots
GROUP BY DB.name
ORDER BY DataFileSizeMB DESC;

Explanation

• sys.master_files contains information about all database files, including data and log files.

• The type column distinguishes file types: 0 for data files and 1 for log files.

• The size is reported in 8KB pages, so multiplying by 8 and dividing by 1024 converts it to megabytes (MB).

• Snapshots are excluded using the source_database_id IS NULL condition.

This query is useful for DBAs who want to monitor and manage database storage more effectively.