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. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights 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 *