As a DBA, you regularly need to know where your database files live, how big they are, and how they’re configured to grow.
Capacity planning, migration prep, new environment review, disk space incidents: they all start here. This script gives you a full picture of every user database file on the instance: physical path, drive letter, current size, max size, and autogrowth settings, all in one result set.
Why It Matters
File layout problems are quiet. They don’t announce themselves until something breaks.
Percentage autogrowth is the classic example. SQL Server’s default is a 10% growth increment. On a 200GB database, that’s a 20GB event blocking every session touching that database until it completes. Most environments I’ve inherited have this on at least some databases. Occasionally all of them.
Data and log files on the same volume is another one. It looks fine until a growth event fills the disk and the log file can’t extend either. At that point nothing can commit and you’re in an incident.
Max size is worth checking too. A file with no cap grows until the volume is full. Knowing which files have unlimited growth tells you where monitoring and alerting need to be.
If you manage multiple instances, consistent file layouts also make migrations and capacity planning far less painful.
The file paths also matter for antivirus exclusions. SQL Server database directories need to be excluded from real-time scanning. Microsoft’s guidance on configuring antivirus for SQL Server covers exactly what to exclude.
The Script
Returns path, drive, size, max size, and autogrowth configuration for every file across all user databases. System databases are excluded.
/*
Script Name : Get-DatabaseFilesDetail
Category : storage-capacity-management
Purpose : Show per-file details for all user databases: path, size, max size, growth settings.
Author : Peter Whyte (https://sqldba.blog/dba-operations/get-database-file-names-and-paths-in-sql-server/)
Requires : VIEW ANY DATABASE
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT
d.name AS database_name,
d.state_desc AS db_state,
d.recovery_model_desc AS recovery_model,
mf.file_id,
mf.name AS logical_name,
mf.type_desc AS file_type,
LEFT(mf.physical_name, 1) AS drive_letter,
mf.physical_name AS physical_path,
CAST(mf.size * 8.0 / 1024 AS DECIMAL(12,2)) AS current_size_mb,
CASE mf.max_size
WHEN -1 THEN NULL
WHEN 0 THEN CAST(mf.size * 8.0 / 1024 AS DECIMAL(12,2))
ELSE CAST(mf.max_size * 8.0 / 1024 AS DECIMAL(12,2))
END AS max_size_mb,
CASE mf.is_percent_growth
WHEN 1 THEN CAST(mf.growth AS VARCHAR(10)) + '%'
ELSE CAST(CAST(mf.growth * 8.0 / 1024 AS INT) AS VARCHAR(20)) + ' MB'
END AS auto_growth,
mf.is_percent_growth AS growth_is_percent,
mf.state_desc AS file_state
FROM sys.master_files AS mf
INNER JOIN sys.databases AS d ON d.database_id = mf.database_id
WHERE d.database_id > 4
ORDER BY d.name, mf.type, mf.file_id;
Reading The Output
One row per database file — data files and log files across all user databases.
| Column | What It Means |
|---|---|
database_name | The database this file belongs to |
db_state | Database state — should be ONLINE in normal operation |
recovery_model | FULL, SIMPLE, or BULK_LOGGED — relevant context for log file sizing |
file_id | Internal file ID — 1 is typically the primary data file, 2 typically the log |
logical_name | The logical name registered in SQL Server |
file_type | ROWS (data file) or LOG (transaction log) |
drive_letter | The drive the file sits on — useful for spotting misplaced files |
physical_path | Full physical path on disk |
current_size_mb | Current allocated file size in MB |
max_size_mb | Maximum allowed size in MB — NULL means no cap configured |
auto_growth | Growth increment shown as MB or % depending on the configured setting |
growth_is_percent | 1 if percentage growth, 0 if fixed MB — any 1 is worth reviewing |
file_state | File state — should be ONLINE |
Production Notes
Change percentage autogrowth to fixed MB increments. Any growth_is_percent = 1 should be updated — 256MB is a sensible default for smaller databases, 1GB or more for large ones. See How to Right-Size SQL Server Database Files.
max_size_mb = NULL means the file grows until the disk is full. That’s not always wrong, but it makes disk monitoring your only safety net.
Data and log files should be on separate volumes. Use the drive_letter column to spot anything sharing a drive — it’s a risk for both capacity and I/O. Log files especially benefit from dedicated volumes.
System databases are excluded (database_id > 4). Remove that filter to see master, model, msdb, or tempdb file config.
To see free space inside each file, use Get Database Sizes and Free Space in SQL Server. This script shows the file configuration; that one shows how full each file currently is.
Part of the health check suite — labelled database-files in Invoke-HealthCheckCollection.ps1.
Related
- Get Database Sizes and Free Space in SQL Server — how much free space is available inside each file right now
- Show Database Growth Events in SQL Server — when files have grown and by how much
- How to Check Transaction Log Space Usage in SQL Server — log file usage and recovery model context
- How to Right-Size SQL Server Database Files — fixing oversized files and poor growth settings
- Configuring antivirus software for SQL Server — Microsoft Docs
- sys.master_files — Microsoft Docs
Get The Scripts
The full scripts are in the dba-tools repo on GitHub. Clone it once if you haven’t already:
git clone https://github.com/peterwhyte-lgtm/dba-tools.git
cd dba-toolsThen run:
# Table output in terminal
.\run.ps1 Get-DatabaseFilesDetail
# Save results as CSV (saves locally to output-files\)
.\run.ps1 Get-DatabaseFilesDetail -OutputFormat Csv
# Against a remote server
.\run.ps1 Get-DatabaseFilesDetail -ServerInstance MYSERVER\INST01sql/monitoring/disk-space/Get-DatabaseFilesDetail.sqlpowershell/wrappers/monitoring/disk-space/Get-DatabaseFilesDetail.ps1
File layout is one of those things that looks fine until it isn’t. Run this on an instance you haven’t checked in a while — you’ll almost always find at least one thing worth changing.
Leave a Reply