SQL Server – Get Size of Database Files

This script gets the size of each database file, not including backups. Essentially, you’ll see .MDF, .NDF, and .LDF files.

SELECT DISTINCT bus.database_name
, (buf.file_size / (1024*1024)) AS [File Size MB]
, buf.physical_name
FROM msdb..backupfile buf
INNER JOIN msdb..backupset bus
ON buf.backup_set_id = bus.backup_set_id
ORDER BY bus.database_name