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.
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)
Size of Filestream is always 0
Can we get the same from Powershell also?
Local vs Remote SQL Instance.