Get SQL Server Database Size, Location for all Databases

Here are the SQL Server queries to get all SQL Server database data file and log file Size, Location:

SELECT DB_NAME(database_id) AS [Database Name],
       Name AS [Logical Name],
       Physical_Name AS [Physical Name],
       (size*8)/1024 AS [Size - MB] 
 FROM sys.master_files WHERE database_id > 4 

This script outputs each data file & log file of all databases.

SQL Query to get Database Location and size


If you want to combine Data File & Log File Size:

SELECT DB_NAME(database_id) AS [Database Name], 
       SUM((size*8)/1024) [Size - MB] 
FROM sys.master_files 
WHERE database_id > 4 
group by DB_NAME(database_id) 
order by DB_NAME(database_id)

This will give you all Databases attached to a specific instance, excluding SQL Server system Databases like Master, Model, etc. (So we’ve: WHERE database_id > 4)

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. Passionate about sharing the deep technical knowledge and experience to help others, through the real-world articles!

2 thoughts on “Get SQL Server Database Size, Location for all Databases

  • Size of Filestream is always 0

    Reply
  • Can we get the same from Powershell also?

    Local vs Remote SQL Instance.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *