Introduction

Very often DBAs need to find the location of the physical file (MDF, LDF & NDF) of a database. Simply we can obtain the output by executing the below query. Run the script to get the SQL Server database file location for all the databases for a particular instance.

Script to find the physical file location

-- T-SQL Author: Soyeluddin Biswas
-- Website: https://ourtechideas.com/
-- Created Date: 25 September 2019

-- Declare and set the server name
DECLARE @ServerName NVARCHAR(128)
SET @ServerName = CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(128))

-- Retrieve the database file details
SELECT
  @ServerName AS [Server Name], -- Server name
  d.name AS [Database Name], -- Database name
  d.database_id AS [Database ID], -- Database ID
  f.name AS [Physical File Type], -- Type of the physical file
  f.physical_name AS [Physical File Location], -- Location of the physical file
  f.state_desc AS [Online Status], -- Online status of the file	
  f.size * 8.00 * 1024.00 AS [Size In Bytes], -- Size of the file in bytes	
  CAST((f.size * 8.00 * 1024.00) / 1048576.00 AS NUMERIC(18,2)) AS [Size In MB], -- Size of the file in megabytes	
  CAST((f.size * 8.00 * 1024.00) / 1073741824.00 AS NUMERIC(18,2)) AS [Size In GB], -- Size of the file in gigabytes	
  CAST((f.size * 8.00 * 1024.00) / 1099511627776.00 AS NUMERIC(18,2)) AS [Size In TB], -- Size of the file in terabytes	
  CAST(CAST(v.total_bytes - v.available_bytes AS FLOAT) / CAST(v.total_bytes AS FLOAT) * 100 AS NUMERIC(18,2)) AS [Used Disk Percent] -- Percentage of disk space used by the file
FROM 
  sys.master_files f
  INNER JOIN sys.databases d ON d.database_id = f.database_id	
  CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) v
ORDER BY
  d.database_id -- Sort by database ID

Result

Exploring the Query

The above query fetches crucial information related to database files. Let’s understand the key columns returned:

1. Server Name: The name of the SQL Server instance on which the query is executed.
2. Database Name: The name of the database.
3. Database ID: The unique identifier of the database.
4. Physical File Type: Specifies the type of the physical file, such as data file or log file.
5. Physical File Location: The path indicating the location of the physical file.
6. Online Status: Indicates whether the file is online or offline.
7. Size In Bytes: The size of the file in bytes.
8. Size In MB: The size of the file in megabytes.
9. Size In GB: The size of the file in gigabytes.
10. Size In TB: The size of the file in terabytes.
11. Used Disk Percent: The percentage of disk space used by the file.