As a Database Administrator, I frequently encounter the need to know all database physical location and size at both the database level or 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 Data file (.mdf) size of All Database
 
Get Log file (.ldf) size of All Database
 
Get all physical file size of All Database
N.B.:
  1. File_ID is file type. 1 for data file (*.mdf) and 2 for log file (*.ldf)
  2. State_Desc is filed online or offline status.
Find all database physical location and size in SQL Server

Leave a Comment