DBA Scripts: Get SQL Server Database File Details

---

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/dba-scripts-get-sql-server-database-file-details/)
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;

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:

• sql/monitoring/disk-space/Get-DatabaseFilesDetail.sql
• powershell/wrappers/monitoring/disk-space/Get-DatabaseFilesDetail.ps1

---

Example Output

---

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:

• Storage and Capacity (hub)
• Database Sizes and Free Space
• Database Free Space Summary
• Transaction Log Size and Usage
• VLF Count
• Disk Space
• Filegroup Space
• Autogrowth History
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Frequently Asked Questions

---

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.