kwizcom banner advertisement

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)
Get SQL Server Database Size, Location for all Databases Get SQL Server Database Size, Location for all Databases Reviewed by Salaudeen Rajack on 4:43 PM Rating: 5

1 comment:

  1. Can we get the same from Powershell also?

    Local vs Remote SQL Instance.

    ReplyDelete

Please Login and comment to get your questions answered!

Powered by Blogger.