DBA Scripts: Get SQL Server Database File Details

July 16, 2026

–

Part of the DBA-Tools Project.

Understanding SQL Server Database File Layout

SQL Server database files are one of those areas where small configuration issues can become large operational problems.

A database may be running perfectly today, but poor file placement, incorrect growth settings, or uncontrolled file expansion can create problems later. Storage issues are one of the most common causes of SQL Server incidents, especially when databases grow faster than expected.

During health checks, migrations, capacity planning, and troubleshooting, DBAs often need a quick answer to simple questions:

Where are my database files stored?
How large are my data and log files?
Are files configured with sensible growth settings?
Which databases are consuming storage?

Get-DatabaseFilesDetail provides a complete inventory of SQL Server database files across an instance, including physical locations, sizes, growth configuration, and maximum limits.

Why Database File Details Matter

SQL Server databases are made up of physical files stored on disk:

Data files (.mdf, .ndf) contain database objects and data
Log files (.ldf) store transaction information required for recovery
Growth settings control how SQL Server expands files when more space is required

Understanding file configuration helps identify:

Databases approaching storage limits
Incorrect file placement
Poor autogrowth configuration
Unexpectedly large transaction logs
Capacity risks before they become incidents

Common issues found during reviews include:

Data and log files stored on the same volume
Very small growth increments causing repeated expansion events
Percentage based growth on large databases
Unlimited file growth consuming available disk space

When To Run This Script

Run this script during:

Routine SQL Server health checks
Storage capacity reviews
Database migrations
SQL Server upgrades
Performance investigations
Unexpected disk growth incidents
Reviewing new database deployments

The Script

Run the following script against your SQL Server instance.

/*
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)
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 'UNLIMITED'
        WHEN 0 THEN CAST(mf.size * 8.0 / 1024 AS VARCHAR(20))
        ELSE CAST(mf.max_size * 8.0 / 1024 AS VARCHAR(20))
    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;

The script queries sys.master_files and returns database file information for all user databases on the SQL Server instance.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

git clone https://github.com/peterwhyte-lgtm/dba-tools

cd dba-tools

.\Initialize-Environment.ps1

.\run.ps1 Get-DatabaseFilesDetail

.\run.ps1 Get-DatabaseFilesDetail -ServerInstance SQLSERVER01

This script lives in the repo at:

<a href=”https://github.com/peterwhyte-lgtm/dba-tools/blob/main/sql/monitoring/disk-space/Get-DatabaseFilesDetail.sql”><code>sql/monitoring/disk-space/Get-DatabaseFilesDetail.sql</code></a>

Example Output

database_name	file_id	logical_name	file_type	physical_path	current_size_mb	max_size_mb	auto_growth
DemoDatabase	1	DemoDatabase_Data	ROWS	D:\MSSQL\DATA\DemoDatabase_Data.mdf	2048	UNLIMITED	512 MB
DemoDatabase	2	DemoDatabase_Log	LOG	D:\MSSQL\LOG\DemoDatabase_Log.ldf	512	2097152	256 MB

Understanding The Results

Physical Path

The physical path shows where SQL Server stores the database files.

Review this to confirm:

Data and log files are separated where appropriate
Files are stored on the expected storage tier
Databases are not accidentally using system drives

Current Size

current_size_mb shows the allocated size of each file.

Remember that allocated size is not the same as used space. A database may have free space inside its files.

Autogrowth Settings

The auto_growth column shows how SQL Server expands the file.

Common recommendations:

Use fixed MB growth rather than percentages
Avoid very small growth increments
Size growth settings based on expected workload

Incorrect autogrowth settings can contribute to:

Frequent file expansion events
Performance pauses
Excessive VLF creation in transaction logs

Best Practices

Regularly review database file configuration as part of SQL Server health checks.

Recommended checks:

Keep data and log files on appropriate storage
Use sensible fixed-size growth increments
Monitor free disk capacity
Avoid unlimited growth without monitoring
Review transaction log sizing separately

Related Scripts

You may also find these scripts useful:

Database Sizes and Free Space
Database Free Space Summary
Transaction Log Size and Usage
VLF Count
Disk Space
Filegroup Space
Database Growth Events

Frequently Asked Questions

How do I find SQL Server database file locations?

Querying sys.master_files provides the physical paths for all database files on an instance.

How do I check SQL Server database file sizes?

Database file sizes can be reviewed using sys.master_files, sys.database_files, or monitoring scripts such as this one.

Should SQL Server database files use percentage growth?

Generally no. Fixed MB growth settings are easier to predict and manage, especially for larger databases.

Summary

Knowing your SQL Server database file layout is a basic but important part of database administration.

This script gives DBAs a quick view of database files, storage locations, sizing, and growth configuration across an entire SQL Server instance.

Run it regularly as part of your SQL Server health checks to catch storage risks before they become production incidents.

Blog Categories

Comments

Leave a Reply Cancel reply