As a DBA, you regularly need to know where your database files live, how big they are, and how they’re configured to grow.
Whether you’re planning disk capacity, reviewing growth settings, troubleshooting space issues, or preparing for a migration, being able to quickly pull data and log file details across an instance is essential.
This post shares:
- A simple SQL script to list file names, paths, sizes, and growth settings
- A small helper snippet for parsing file names from full paths, useful in scripts and automation
Why Database File Paths Matter
Knowing where your database files are stored is useful for more than just curiosity.
In day-to-day DBA work, this information often comes up when:
- Checking disk space usage and growth trends
- Verifying data and log file placement
- Reviewing growth settings before incidents happen
- Ensuring database directories are excluded from antivirus scans
- Preparing for server builds or migrations where consistent layouts matter
If you manage multiple SQL Server instances, standardising data and log file locations makes life much easier. Capacity planning is simpler, migrations are cleaner, and troubleshooting becomes far less guessy.
Get Database File Names and Paths
The query below returns data and log file information for all databases on the instance, including file names, physical paths, sizes, and growth configuration.
This is intended as a read-only, instance-wide view of file layout and configuration.
-- Get db filenames, paths, sizes and growth info
SELECT DB_NAME(database_id) AS 'Database Name',
file_id, name, physical_name, type_desc, state_desc,
is_percent_growth, growth,
CONVERT(bigint, growth/128.0) AS 'Growth in MB',
CONVERT(bigint, size/128.0) AS 'Total Size in MB', max_size
FROM sys.master_files WITH (NOLOCK)
ORDER BY DB_NAME(database_id), file_id OPTION (RECOMPILE);
This gives you a clear view of:
- Which files belong to which databases
- Whether a file is data or log
- Where it lives on disk
- How it grows and how large it currently is
In larger environments, you may prefer converting sizes to GB rather than MB. Adjusting the calculation is trivial if needed.
In this example, some database files are shown on the local disk for test purposes only. In production environments, user database data and log files should be placed on dedicated volumes rather than the C: drive.
Note on System Databases
The results include system databases such as ‘Master’, ‘Model’, ‘MSDB’, and ‘TempDB’.
This is expected, as sys.master_files tracks files at the instance level.
👉 Microsoft Docs: sys.master_files (Transact-SQL)
Parsing a File Name from a Full Path in SQL
Sometimes you already have a full file path and just want the file name. This comes up more often than you might expect when building scripts or reports.
The snippet below extracts the file name from a full path:
-- Parse file name from full path
DECLARE @FullFilePath NVARCHAR(500) = 'C:\Data\MyDatabase.mdf';
SELECT
RIGHT(@FullFilePath, CHARINDEX('\', REVERSE(@FullFilePath)) - 1) AS [File Name];This is a small thing, but it’s handy when you’re working with file metadata or generating dynamic SQL.
Common Follow-ups
After identifying database file locations, common next steps include:
- Checking free disk space on the underlying volumes
- Verifying data and log file separation aligns with platform standards
- Checking database file sizes and available free space to understand how close files are to capacity (see Get Database Sizes and Free Space in SQL Server).
- Checking transaction log space usage to ensure log files are not approaching capacity (see How to Check Transaction Log Space Usage in SQL Server).
- Reviewing database growth events to understand when and why files are expanding (see Show Database Growth Events in SQL Server).
Final Thoughts
Database file layout is one of those fundamentals that quietly underpins everything else. When you know what files exist, where they live, and how they grow, you avoid surprises and make better decisions long before issues show up.
This is one of those scripts worth keeping close. It’s simple, fast, and useful far more often than you might expect.
Leave a Reply