Check Database Physical File Paths and Sizes in SQL Server – DBA Guide

As a Database Administrator, I frequently encounter the need to know all Database Physical File Paths and Sizes in SQL Server at both the database level and the file level. We can go into SQL Server Management Studio (SSMS) and expose the database properties and see information for the individual files: their creation size, logical and physical names, and auto grow settings, but I can’t see how “full” those individual files are currently.

Sometimes I need this information for all the databases and sometimes just a single database. I needed to find the size of the log file, file location. Here is the script, if you remove the WHERE condition you will find the result for all the databases.

Get the data file (.mdf) size of all databases

SELECT d.NAME, (F.SIZE*8)/1024 AS Size_MB, F.PHYSICAL_NAME AS CURRENT_DATABASE_LOCATION 
FROM sys.master_files F
INNER JOIN sys.databases D ON D.database_id=F.database_id
WHERE F.FILE_ID=1 AND F.STATE_DESC='ONLINE' AND D.NAME NOT IN ('master','tempdb','model','msdb')

Get the log file (.ldf) size of all databases

SELECT d.NAME, (F.SIZE*8)/1024 AS Size_MB, F.PHYSICAL_NAME AS CURRENT_DATABASE_LOCATION 
FROM sys.master_files F
INNER JOIN sys.databases D ON D.database_id=F.database_id
WHERE F.FILE_ID=2 AND F.STATE_DESC='ONLINE' AND D.NAME NOT IN ('master','tempdb','model','msdb')

Get database physical file paths and sizes

SELECT d.NAME, (F.SIZE*8)/1024 AS Size_MB, F.PHYSICAL_NAME AS CURRENT_DATABASE_LOCATION 
FROM sys.master_files F
INNER JOIN sys.databases D ON D.database_id=F.database_id
WHERE F.STATE_DESC='ONLINE' AND D.NAME NOT IN ('master','tempdb','model','msdb')

N.B.:

1. 1. File_ID is the file type. 1 for data file (*.mdf) and 2 for log file (*.ldf)
   2. State_Desc is filed in online or offline status.

Database Physical File Paths and Sizes